Proper Way to Truncate Log After Performing Full Backup (SQL Server 2000)

Hello all - I have a SQL Server 2000 database setup using the Full Recovery Model. Each night, we backup the entire database, and as such would like to truncate the log at this time as well.

Is the best way to do this to also backup the Transaction Log, and then perform a DBCC SHRINKFILE command? It just seems like there should be an easier way...?

Thanks!

Let's back up a few steps here.

What are you gaining by shrinking the transaction log every night?

The transaction log is designed to continually reuse its space internally. Whenever you back up the transaction log, all virtual log files (logical areas within the physical log file) which are not involved in any current transaction are marked as "free" and can be reused.

So, one way to manage the growth of a transaction log is to vary the frequency of log backups. The more frequent the log backup, the less data needs to be stored in the log (and the less data is lost in the event of a disaster).

Once the log reaches a steady-state, it shouldn't grow unless some exceptional update happens.

When you continually shrink and grow the file, you end up with filesystem fragmentation, which isn't good for performance. Also, the log file growth itself takes time.

Prompts in Model Designer

I would like to set up a prompt / filter as part of the model that carries through to the report builder. I have a query where I would like to force a prompt on the user as part of any report they create. Ideally, I would like to have it default as well.

I noticed that there is a prompt attribute in model designer. When I go to add the filter attribute in model designer, I get the same dialog box I get in the report builder - except it does not have the prompt as an option. Is there a way to accomplish this?

Thanks

There is no way to specify in the report model that filter conditions created for a given attribute should be set to "Prompt" by default.

It's an interesting request, though. If you have a minute, I'd be interested to hear more about why this feature is important to you. You can contact me through my blog.

|||

For the same reason an administrator might set up Prompts on Reports in RS...RB reports are no different. We might want to impose certain filters...either to guide users in report building, or perhaps to limit the amount of resources in querying. the reasons are too numerous to list.

Having the capability on the user front-end but not the development back-end is a deficiency.

sql

Prompts in Model Designer

I would like to set up a prompt / filter as part of the model that carries through to the report builder. I have a query where I would like to force a prompt on the user as part of any report they create. Ideally, I would like to have it default as well.

I noticed that there is a prompt attribute in model designer. When I go to add the filter attribute in model designer, I get the same dialog box I get in the report builder - except it does not have the prompt as an option. Is there a way to accomplish this?

Thanks

There is no way to specify in the report model that filter conditions created for a given attribute should be set to "Prompt" by default.

It's an interesting request, though. If you have a minute, I'd be interested to hear more about why this feature is important to you. You can contact me through my blog.

|||

For the same reason an administrator might set up Prompts on Reports in RS...RB reports are no different. We might want to impose certain filters...either to guide users in report building, or perhaps to limit the amount of resources in querying. the reasons are too numerous to list.

Having the capability on the user front-end but not the development back-end is a deficiency.

Programmically change Database MODEL

Hi all,
Is it possible to change the model of the Database thur code?
I am trying to automate the task of changing the database from one Model to
Simple and back. It is easy to do?
Any leads would be nice.
Thank Youfound the answer. thanks
"Binh Phung" wrote:

> Hi all,
> Is it possible to change the model of the Database thur code?
> I am trying to automate the task of changing the database from one Model t
o
> Simple and back. It is easy to do?
> Any leads would be nice.
> Thank You

Programmically change Database MODEL

Hi all,
Is it possible to change the model of the Database thur code?
I am trying to automate the task of changing the database from one Model to
Simple and back. It is easy to do?
Any leads would be nice.
Thank You
found the answer. thanks
"Binh Phung" wrote:

> Hi all,
> Is it possible to change the model of the Database thur code?
> I am trying to automate the task of changing the database from one Model to
> Simple and back. It is easy to do?
> Any leads would be nice.
> Thank You

Programmatically Set Sever Timeout Using AMO Classes and Objects

Using DSO object model, We use to Specify the Server Timeout for 4 hours as follows:

oServer.Timeout = 14400

But using the AMO Classes and Objects, How will I set the Server timeout programmatically?

Many Thanks

Subhash Subramanyam

hello Subhash,

i think you can specify the Timeout property in connection string. I.e. something like this:

Server server = new Server();

server.Connect("Data Source=localhost; Timeout=14400;");

// do something

server.Disconnect();

(there is also a Timeout property on DataSource object - though that one affects data source connection i think).

hope this helps,

|||

Hi Mary,

Thanks for the reply, I got what I was looking for.

Programmatically generating a reporting model?

Is there documentation/examples on how to programmatically generate a report model (.smdl XML file)? The situation I'm in would make generating the model manually via Visual Studios difficult (would have to hire lots of people).

Any ideas or suggestions would be greatly appreciated.

One option is to download the XSD here and generate an object model from it:

http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd

Programmatically create a Report Model SMDL File.

Hello,

I'm looking for Programmatically genration of Report Model .smdl file.

Please guide me or suggest some links so that ui can refer same.

Is there any smdl genrator or any classes for the same...?

Thanks in advance.

Sandip

Sandip,

Did you find the answer? As I am also struggling with the samilar question and was not able to get any heads up.

I am trying to understand whether we can create SMDL automatically or can we use objects rather than using databases.

Atul

|||I am hoping that this points you in the right direction:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359637&SiteID=1

Programmatically create a Report Model SMDL File.

Hello,

I'm looking for Programmatically genration of Report Model .smdl file.

Please guide me or suggest some links so that ui can refer same.

Is there any smdl genrator or any classes for the same...?

Thanks in advance.

Sandip

Sandip,

Did you find the answer? As I am also struggling with the samilar question and was not able to get any heads up.

I am trying to understand whether we can create SMDL automatically or can we use objects rather than using databases.

Atul

|||I am hoping that this points you in the right direction:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=359637&SiteID=1

Programmatically create a Report Model

We would like to take advantage of the Report Builder tool to provide
end-user ad-hoc reporting. The database for the application can be
different depending on how the client configures it (they can add various
fields and data collection forms). At TechEd, I attended a session on
Microsoft Dynamics AX 4.0 and its tight integration with Reporting
Services/Report Builder. This is very much in line with what we'd like to
do. I imagine the users adding a field (through our interface), setting a
few properties, and then after some automated process occurs, they would be
able to use Report Builder to access any data collected in that field.
What we'd like to do is programmatically generate and maintain a report
model based on how the individual install is configured. I've found some
documentation that makes me believe this is possible, but I'm hoping someone
can nudge me in the right direction. The ReportingServices2005 class has
several model related methods (CreateModel) and I found the .xsd for the
semantic model XML file
(http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd),
but no real documentation on how to put together a Report Model document
from scratch. Looking at the files generated from BIDS is somewhat
overwhelming and I have no idea where I'd pull most of the information.
Any help that can be provided would be greatly appreciated. Thanks!
RussellHi Russell,
Thank you for your posting!
From your description, my understanding of this issue is: You want to
programmatically manage the Report Model xml file (smdl). If I
misunderstood your concern, please feel free to let me know.
I would like to know what information you want to pull from the Report
Model. The smdl file is a xml file and I think you could control the file
in VS .NET.
The CreateModel of the Reporting Services 2005 class is used to upload the
Report Model file to Report Server. I think it is not quite meet your
requirement.
Here is an article about refine a report model.
Refining a Report Model
http://www.sqlmag.com/Article/ArticleID/50048/sql_server_50048.html
Hope this information will be helpful and please let me know the result.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Russell,
I would like to know whether the information is helpful. Feel free to let
me know if you have any questions.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Russel
did you have any success with that? did you solve it by creating the SMDL
File with an XMLTextWriter or did you find an other solution? an oblect that
could be referenced?
i am having the same problem and would like to establish the SMDL file fully
by code, from scratch.
any info is appreciated.
Kind Regards, Rene
"RussellReed" schrieb:
> We would like to take advantage of the Report Builder tool to provide
> end-user ad-hoc reporting. The database for the application can be
> different depending on how the client configures it (they can add various
> fields and data collection forms). At TechEd, I attended a session on
> Microsoft Dynamics AX 4.0 and its tight integration with Reporting
> Services/Report Builder. This is very much in line with what we'd like to
> do. I imagine the users adding a field (through our interface), setting a
> few properties, and then after some automated process occurs, they would be
> able to use Report Builder to access any data collected in that field.
> What we'd like to do is programmatically generate and maintain a report
> model based on how the individual install is configured. I've found some
> documentation that makes me believe this is possible, but I'm hoping someone
> can nudge me in the right direction. The ReportingServices2005 class has
> several model related methods (CreateModel) and I found the .xsd for the
> semantic model XML file
> (http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd),
> but no real documentation on how to put together a Report Model document
> from scratch. Looking at the files generated from BIDS is somewhat
> overwhelming and I have no idea where I'd pull most of the information.
> Any help that can be provided would be greatly appreciated. Thanks!
> Russell
>
>

Programmatically create a Report Model

Hello,

What we'd like to do is programmatically generate and maintain a report model based on how the individual install is configured. I've found some documentation that makes me believe this is possible, but I'm hoping someone can nudge me in the right direction. The ReportingServices2005 class has several model related methods (CreateModel) and I found the .xsd for the semantic model XML file
(http://schemas.microsoft.com/sqlserver/2004/10/semanticmodeling/SemanticModeling.xsd),
but no real documentation on how to put together a Report Model document from scratch. Looking at the files generated from BIDS is somewhat overwhelming and I have no idea where I'd pull most of the information.

Finally, I want to create Report Model(.smdl) file without Report Model Designer.

I want to Implement CreateModel Method to create new model, any source code sample..?

Any help that can be provided would be greatly appreciated. Thanks!

Sandip.

I am not positive but I don't think that the CreateModel method is for what you think it is for. I think it is for creating an instance of the model, uploading it to a ReportServer. IOW you read in an SMDL file and push it to a server, update it -- various management purposes.

Or I could be looking at a different part of the RS API than you are, and you could be right <s>.

IAC, fond as I am of reading schemas and creating XML files from scratch, this one is tough because of all the guids. Probably your best bet would be to start with a basic one and add nodes into it based on the privileges and configuration for an individual install or user, for example adjust the data sources or adding some fields.

>L<

|||

How can I update report models in the report server programmatically?

I was able to publish report models from one server to another using .rss file. I used the method ".createmodel". However, this doesn't have the overwrite parameter unlike ".createreport" method.

Is there a way or command to do this?

Any help would be greatly appreciated(",). Thanks.|||

To start with, I should say that I'm not clear enough about models to understand why you think there are associated parameters. I don't understand the connection, I'm not that familiar with models and didn't think they had parameters.

IAC, yes there is some scripting associated with models and the best advice I can give you is to become familiar with the scripting utilitiy you'll find here: http://www.sqldbatips.com/showarticle.asp?ID=62 It will interrogate your reporting services server and generate scripts for you (including models). And you will learn a lot from what it shows. There is an example of the script it generates here: http://www.sqldbatips.com/samples/code/RSScripter/readme.htm#_script_model

>L<

|||

Lisa,

Thanks. Actually, I was able to find the answer on the same site you've given me( http://www.sqldbatips.com/showarticle.asp?ID=62 ) ...

I downloaded the scripter, run rsscripter.exe, on the scripting options>data/model, i checked update existing model and generate the script. On the generated script, I noticed that they have used .SetModelDefinition method to do this. So, to sum up:

.SetModelDefinition - used to update report models in the report server

.CreateReport - used to create/add report models in the report server

Thanks again

Programmatically checking whether Integration service is running or not

I am building an application that uses .NET 2.0 transaction feature. Since .NET 2.0 transaction model uses SQL Server 2005 Integration services for its operation, I want to check from my application (.NET 2.0 code) whether the Integration service is running on SQL box or not? Is there a way in which we can accomplish this task?

Prashant

You can query the SSIS service with the GetRunningPackages method to get the packages running on a server.

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.application.getrunningpackages.aspx
|||Using GetRunningPackages is perhaps not quite the same as saying is the windows service running, which seemed to be the original question. The fact that the service supports that method would probably allow you to infer that result, but maybe you would want to find out more about the service or control it, in which case have a look at the System.ServiceProcess.ServiceController class. The System.ServiceProcess namespace contains classes for manging Windows Services, not anything specific to SSIS.

Programmatic "Script Table as Create to..." in T-SQL?

Is there a way to write a T-SQL query against the database object model to create the same output as you would get when you right-click the object in Management Studio and select 'Script table as Create to.."?

Or better yet, is there a way to retrieve the DDL that was used to create a table using a T-SQL query?

Thanks in advance,
-Preston M. Price

Perhaps something like this:

SELECT text

FROM sys.syscomments

WHERE id = object_id( 'MyStoredProcedure' )

|||

There is no way to query DDL from the database (beside the Routine_definition your get from INFORMATION_SCHEMA.Routines table or the sysobejcts as Arnie pointed out), you will have to either write your own way to do this or you can use API of SMO to do the stuff with the scripter object.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Programmatic "Script Table as Create to..." in T-SQL?

Is there a way to write a T-SQL query against the database object model to create the same output as you would get when you right-click the object in Management Studio and select 'Script table as Create to.."?

Or better yet, is there a way to retrieve the DDL that was used to create a table using a T-SQL query?

Thanks in advance,
-Preston M. Price

Perhaps something like this:

SELECT text

FROM sys.syscomments

WHERE id = object_id( 'MyStoredProcedure' )

|||

There is no way to query DDL from the database (beside the Routine_definition your get from INFORMATION_SCHEMA.Routines table or the sysobejcts as Arnie pointed out), you will have to either write your own way to do this or you can use API of SMO to do the stuff with the scripter object.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

Programmaing with SSIS Object model

Hi there,

Can anyone point me to some sample source codes or any articles that describes how I can programmatically create a package which will import data from a flatfile (csv) to Sql server database.

I know there is some example that describes exporting data from sql server to flatfiles. Anyway I have failed to accomplish my goal by following those examples.

If anyone have a code snippet to do that please help me with that.

Thanks in advance

Moim Hossain

If you want, post the code you have already to build the SSIS package (or a link to it). This response is not the code snippet you're looking for, but it sounds like the package builder code you already have (from the other post), is not far from working.|||

jaegd ,

Sorry I did not understand. Should I post my code to you?

I have already posted my code here into another posting. you can take a look on my code here http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1073656&SiteID=1

Can you tell me where I am going wrong?

Thanks

Moim

Programatically Evaluating SSIS Expression

Is there an object in the DTS object model that will allow me to evaluate an SSIS expression? I am trying to build a custom task that will require re-evaluation of an expression multiple times within the execute method and I can't seem to find a way to do this.

Thanks,

Adam

Add a reference to Microsoft.DataTransformationServices.Controls

Use the Microsoft.SqlServer.Dts.Runtime.Wrapper.ExpressionEvaluatorClass class. You will want to use DTSInfoEvents to capture error details when calling Evaluate or Validate. Pass the events to the Events property of the ExpressionEvaluatorClass.

Take a look at the File Watcher Task (http://www.sqlis.com/) for an example of this in action, just set an expression through the task UI to see the Expression Editor Dialog we have built in action. It use the ExpressionEvaluatorClass behind the scenes to provide the evaluation functionality.

Not documented, so not supported, but it works.|||As Darren said, this is not documented and will not be supported by Microsoft. It could change at anytime and break your component if you use it.
User beware.|||

I have logged a bug to document and publicly expose this, but it was on Beta Place, so if someone wants to do the Product Feedback thing I'll vote for it.