Gererating Dynamic Queries During Run Time

How to Gererating Dynamic Queries During Run Time and execute the results

Thanks in advance

Suresh

You will have to use dynamic SQL.

I highly recommend that you review these articles -they will guide you, and they will explain the cautions and dangers.

Dynamic Search Conditions in T-SQL
http://www.sommarskog.se/dyn-search.html

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

sql

Gerating sql script with default

Hello there
I have database that i add on there default
On the nornal script the tables are run first. Now the tables cannot be
created because they are based on the default
What i need to do to generate the script from now
Or how can i cancel the default?
need assistance imergancyI assume you are talking about a default object. You can create default
objects with:
CREATE DEFAULT <default name> AS <expression>
If you put this in your script to create the default before you create the
tables, thing should work fine.
Note that defaults created in this way are a backward compatibility feature,
and to ensure that your code will work with future versions of SQL Server,
you should declare DEFAULTs as column constraints.
Jacco Schalkwijk
SQL Server MVP
"Roy Goldhammer" <roygoldh@.hotmail.com> wrote in message
news:%23Tm4r8hKFHA.436@.TK2MSFTNGP09.phx.gbl...
> Hello there
> I have database that i add on there default
> On the nornal script the tables are run first. Now the tables cannot be
> created because they are based on the default
> What i need to do to generate the script from now
> Or how can i cancel the default?
> need assistance imergancy
>
>|||Can't you just edit the script and put the DEFAULTs at the beginning?
CREATE DEFAULT is virtually obsolete so I would go with the
recommendation in Books Online: avoid it and use Default Constraints
instead.
David Portas
SQL Server MVP
--|||Thankes
So how can i get rid of it now?
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110974952.692560.119730@.f14g2000cwb.googlegroups.com...
> Can't you just edit the script and put the DEFAULTs at the beginning?
> CREATE DEFAULT is virtually obsolete so I would go with the
> recommendation in Books Online: avoid it and use Default Constraints
> instead.
> --
> David Portas
> SQL Server MVP
> --
>|||To get rid of the error message just put in the CREATE DEFAULT
statements. To get rid of the defaults altogether you'll have to
replace all references to sp_bindefault with an ALTER TABLE... ADD
CONSTRAINT statement instead. For example:
EXEC sp_bindefault 'default_name', 'table_name.column_name'
should become:
ALTER TABLE table_name
ADD CONSTRAINT df_constraint_name
DEFAULT (<default value> ) FOR column_name
David Portas
SQL Server MVP
--|||Thankes David
I found out another way to do this?
sp_unbinddefault 'table_name.field_name'
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110977180.528780.196840@.f14g2000cwb.googlegroups.com...
> To get rid of the error message just put in the CREATE DEFAULT
> statements. To get rid of the defaults altogether you'll have to
> replace all references to sp_bindefault with an ALTER TABLE... ADD
> CONSTRAINT statement instead. For example:
> EXEC sp_bindefault 'default_name', 'table_name.column_name'
> should become:
> ALTER TABLE table_name
> ADD CONSTRAINT df_constraint_name
> DEFAULT (<default value> ) FOR column_name
> --
> David Portas
> SQL Server MVP
> --
>|||Be aware that sp_unbindefault will disable the functionality of the
default. Inserts that don't specify explicit values for a column will
therefore atempt to populate the column with NULL. The insert will fail
if the column is not nullable.
David Portas
SQL Server MVP
--

Generating SQL Scripts

Hi,
How can I generate a script file for all the tables in my database with the data. The script file when run should create all the tables with the constraints and also have the data inserted into them.
Thanks in advance
P.C. VaidyanathanYou can generate the schema easily by going to the Enterprise Manager and right clicking on the dababase name "All Tasks","Generate SQL Scripts". I don't know of anything to script inserting the data. Can you use DTS? Not only will it create the schema and load the data but it will generate all the files used to create the schema.|||I agree with Paul Young, DTS would be your best option to create the tables, load the data nad maybe even do aditional preprocessing to your tables.

This is how we do it on a daily basis with fairly large tables all the time. Realizing size is relative, I am talking about databases as small as a few thousand rows to ones as large as 200 million.

Generating overnight data vs Live - suggestions needed.

We have an MIS system which has approx 100 reports. Each of these
reports can take up to several minutes to run due to the complexity of
the queries (hundreds of lines each in most cases). Each report can be
run by many users, so in effect we have a slow system.

I want to seperate the complex part of the queries into a process that
is generated each night. Then the reports will only have to query
pre-formatted data with minimal parameters as the hard part will have
been completed for the users when they are not in. Ideally we will
generate (stored procedure possibly) a set of data for each report and
hold this on the server. We can then query with simpler parameters
such as by date and get the data back quite quickly.

The whole process of how we obtain the data is very complex. There are
various views which gather data from the back office system. These are
very complex and when queries are run against them including other
tables to bring in more data, it gets nicely complicated.

The only problem is that the users want to have access to LIVE data
from the back office system, specifically the Sales team who want to
access this remotely. My method only allows for data from the night
before, so is there an option available to me which will allow me to
do this ? The queries can't be improved on an awful lot, so they will
take as long as they take. The idea of running them once is the only
way I can see to improve the performance in any significant way.

True I could just let them carry on as they are and let them suffer
with the performance on live data, but I'd like to do something to
improve the situation for them.

Any advice would be appreciated.

Thanks

RyanYep, that definitely makes a difference. Taken me a while to get back
to this, but we're looking at a solution along these lines.

By taking a copy of the data from the view into a table and then
replicating this onto another database (for generating) and then
running stored procedures (from each complex query) against this,
we've been able to reduce the time from 5 minutes for a test generate
query to about 3 seconds. We've timed the other parts and can probably
generate all of our data in less than 5 minutes for 100 complex
queries that would normally take hours.

Once this data is generated we'll simply swap it for the current data
which should be pretty quick. Our report now loads in less than a
second as opposed to 5 mins or so.

This means we can probably update the data once an hour and push it
onto the query database which will be more than quick enough for the
users. This has the knock on effect of being able to produce the
reports in seconds instead of minutes.

Erland, thanks for the advice on this it should make quite a
difference.

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns93DEF27F53F61Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > The only problem is that the users want to have access to LIVE data
> > from the back office system, specifically the Sales team who want to
> > access this remotely. My method only allows for data from the night
> > before, so is there an option available to me which will allow me to
> > do this ? The queries can't be improved on an awful lot, so they will
> > take as long as they take. The idea of running them once is the only
> > way I can see to improve the performance in any significant way.
> If users want current data, you cannot pre-compute it for them. You
> know, have the cake and eat it.
> What you could to do is to set up a report server which you replicate
> to, so that you take the load of the back-office system.
> Yet another alternative, is to have three databases:
> A - the source-system.
> B - the view system that the users use.-
> C - a computation system.
> You keep the computation database updated by means of log shipping
> (this is better than replication in this case). One you have applied
> a log, you pre-compute all data. When this step is done, you flush the
> pre-computed tables on B, and insert the data from C. Now you go and
> pick up a new log from A. A more fancy variant is to have a seamless
> switch between B and C which you flip constantly. I believe there are
> such things.|||Ryan (ryanofford@.hotmail.com) writes:
> By taking a copy of the data from the view into a table and then
> replicating this onto another database (for generating) and then
> running stored procedures (from each complex query) against this,
> we've been able to reduce the time from 5 minutes for a test generate
> query to about 3 seconds. We've timed the other parts and can probably
> generate all of our data in less than 5 minutes for 100 complex
> queries that would normally take hours.

300 seconds down to three! That's not bad!

> Erland, thanks for the advice on this it should make quite a
> difference.

I'm only glad to have helped! And thanks for reporing back!

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Generating Multiple Reports

I have a report that I pass parameters of a clientID. I run the report and export it to a .pdf file. I would like to be able to do this for multiple clients without manually having to enter the ID each time and exporting it.

I have thought about creating a report that calls this report so that I can pass in the clientID one at a time. The problem is trying to export each one.

Does anyone have any ideas? Thanks in advance!

I beleive you can use the reportviewer.render method to export directly to a PDF. I've done a similar app going straight to printer.|||

Let me quote myself from http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2048825&SiteID=1

Two methods come to mind:

1. You can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50Jqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Within the VB script that you run, query your tables and feed the parameters.

2. You could use a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

http://msdn2.microsoft.com/en-us/library/ms169673.aspx

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

and I believe either method will work for you here. If you need a more specific answer, let me know.

Good luck,

Larry Smithmier

Generating a cached copy of the report

Is there a way to programatically determine if a report should be generated from the cache or run against real-time data?

I have a situation wherein I would need to programmatically figure out if the datasource needs to be a cached version or real-time data based on certain options that a user defines (not report parameters)...

any help would be very appreciated.Not before running the report, but afterwards you can look at the ExecutionDate property. Depending on the version of SRS you are using that can be retrieved in different ways.|||

NOt sure what you're trying to do: data sources are not cached in Reporting Services. Report are.

Essentially, if you want cache to work, you need to set the data source to use stored credentials and not use any User!UserID or User!Language references in your report.

The GetCacheOptions method provides caching information on the report itself.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_5jas.asp

-Lukasz

|||

Yes, I would like to have the report cached with the underlying data in it.

If I were to have it cached per UserID, would having references to User!UserID in the report take care of it?

Sorry if this is a pretty basic question, but I am trying to finalize if this is possible using Reporting services.
Thanks

|||The cache is not per user, it is shared between users (based on parameters). You can use a post query filter on UserID but referenceing the user it in the query doesn't make any sense. All of the caching options are discussed in the online help.|||If the report uses UserID in a post-query filter, the report will get cached, but the filter will be re-executed for each user.|||

I am trying to get around the user global variable to run reports. I have security implemented by User in the reports. i.e. a userID is only allowed to select the following regions in the region parameter,

I also have a hidden parameter that has two values; online and offline. The parameter is defaulted to online when running the reports interactively and for subscriptions to batch. When it is online, I get the current logged on userID using a CLR function. We get around the batch parameters by using a master userID that has access to all the data (all regions) in the subscriptions.

When the subscription is run with batch, will the interactive users pick up the cached report or run it again as the interactive parameter is online? From your reply, the cache that is selected is based on the parameter list for the report.

The real question is caching based on the parameters passed to retrieve from cache or is there a way around it? Or is there a way to determine if a cached version of the report is being run so that I can plug in my master userID?

Thanks

Generating a cached copy of the report

Is there a way to programatically determine if a report should be generated from the cache or run against real-time data?

I have a situation wherein I would need to programmatically figure out if the datasource needs to be a cached version or real-time data based on certain options that a user defines (not report parameters)...

any help would be very appreciated.Not before running the report, but afterwards you can look at the ExecutionDate property. Depending on the version of SRS you are using that can be retrieved in different ways.|||

NOt sure what you're trying to do: data sources are not cached in Reporting Services. Report are.

Essentially, if you want cache to work, you need to set the data source to use stored credentials and not use any User!UserID or User!Language references in your report.

The GetCacheOptions method provides caching information on the report itself.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rsprog/htm/rsp_ref_soapapi_service_ak_5jas.asp

-Lukasz

|||

Yes, I would like to have the report cached with the underlying data in it.

If I were to have it cached per UserID, would having references to User!UserID in the report take care of it?

Sorry if this is a pretty basic question, but I am trying to finalize if this is possible using Reporting services.
Thanks

|||The cache is not per user, it is shared between users (based on parameters). You can use a post query filter on UserID but referenceing the user it in the query doesn't make any sense. All of the caching options are discussed in the online help.|||If the report uses UserID in a post-query filter, the report will get cached, but the filter will be re-executed for each user.|||

I am trying to get around the user global variable to run reports. I have security implemented by User in the reports. i.e. a userID is only allowed to select the following regions in the region parameter,

I also have a hidden parameter that has two values; online and offline. The parameter is defaulted to online when running the reports interactively and for subscriptions to batch. When it is online, I get the current logged on userID using a CLR function. We get around the batch parameters by using a master userID that has access to all the data (all regions) in the subscriptions.

When the subscription is run with batch, will the interactive users pick up the cached report or run it again as the interactive parameter is online? From your reply, the cache that is selected is based on the parameter list for the report.

The real question is caching based on the parameters passed to retrieve from cache or is there a way around it? Or is there a way to determine if a cached version of the report is being run so that I can plug in my master userID?

Thanks

Generate Script Problem

Hi,

When I run the "Generate Scripts.." wizard to generate Stored Procs, they are displayed with all the single quotes replaced by two single quotes and a few additional single quotes here and there. The problem is, I can not take this stored proc and run it in another DB as you can see it from the example below. Do I need to be picking any particular settings?

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER OFF

GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[getQuestions]') AND type in (N'P', N'PC'))

BEGIN

EXEC dbo.sp_executesql @.statement = N'

/*************************************************************************************

Stored Proc Desription:

*************************************************************************************/

CREATE PROCEDURE [dbo].[getQuestions]

AS

SELECT a, b, c FROM Country WHERE countrycode=''US''

'

END

The above is not a problem. Please ignore this post. Everything works as intended.

Generate 2k compatible script of 2k5 DB?

Does anyone know of a tool that will generate backwards compatible
scripts of an SQL Server 2005 database (so that it can be run on SQL
Server 2000 to create the DB and add the data)? I have views, stored
proc's, triggers, a function, and some roles that need to go with the
tables and their data as well. I've tried generating a script from SQL
Server management studio, but there are tons of errors when I try to
run it on a 2k server, even after I fix the syntax ones. There *has*
to be an easier way of doing this. I apologize if this is something
that is very well known, but this is the first time I've had to do it,
and I haven't found anything that looked like it would do it by
searching.
Thanks,
Oliver Garraux
There is an option in the script wizard (script for server version) to
generate 2000 compatible code that you must check. But if you used some of
the 2005 features you may not be able to do this completely.
Andrew J. Kelly SQL MVP
"Oliver Garraux" <olrbengax@.gmail.com> wrote in message
news:1168220255.369875.244510@.s34g2000cwa.googlegr oups.com...
> Does anyone know of a tool that will generate backwards compatible
> scripts of an SQL Server 2005 database (so that it can be run on SQL
> Server 2000 to create the DB and add the data)? I have views, stored
> proc's, triggers, a function, and some roles that need to go with the
> tables and their data as well. I've tried generating a script from SQL
> Server management studio, but there are tons of errors when I try to
> run it on a 2k server, even after I fix the syntax ones. There *has*
> to be an easier way of doing this. I apologize if this is something
> that is very well known, but this is the first time I've had to do it,
> and I haven't found anything that looked like it would do it by
> searching.
> Thanks,
> Oliver Garraux
>
|||Thanks a bunch, I don't know why I didn't see that option in there. It
worked great for everything except a trigger and a view, and those
worked just fine with copying and pasting stuff. I appreciate your
help.
Oliver Garraux
Andrew J. Kelly wrote:
> There is an option in the script wizard (script for server version) to
> generate 2000 compatible code that you must check. But if you used some of
> the 2005 features you may not be able to do this completely.
> --
> Andrew J. Kelly SQL MVP

Generate 2k compatible script of 2k5 DB?

Does anyone know of a tool that will generate backwards compatible
scripts of an SQL Server 2005 database (so that it can be run on SQL
Server 2000 to create the DB and add the data)? I have views, stored
proc's, triggers, a function, and some roles that need to go with the
tables and their data as well. I've tried generating a script from SQL
Server management studio, but there are tons of errors when I try to
run it on a 2k server, even after I fix the syntax ones. There *has*
to be an easier way of doing this. I apologize if this is something
that is very well known, but this is the first time I've had to do it,
and I haven't found anything that looked like it would do it by
searching.
Thanks,
Oliver GarrauxThere is an option in the script wizard (script for server version) to
generate 2000 compatible code that you must check. But if you used some of
the 2005 features you may not be able to do this completely.
--
Andrew J. Kelly SQL MVP
"Oliver Garraux" <olrbengax@.gmail.com> wrote in message
news:1168220255.369875.244510@.s34g2000cwa.googlegroups.com...
> Does anyone know of a tool that will generate backwards compatible
> scripts of an SQL Server 2005 database (so that it can be run on SQL
> Server 2000 to create the DB and add the data)? I have views, stored
> proc's, triggers, a function, and some roles that need to go with the
> tables and their data as well. I've tried generating a script from SQL
> Server management studio, but there are tons of errors when I try to
> run it on a 2k server, even after I fix the syntax ones. There *has*
> to be an easier way of doing this. I apologize if this is something
> that is very well known, but this is the first time I've had to do it,
> and I haven't found anything that looked like it would do it by
> searching.
> Thanks,
> Oliver Garraux
>|||Thanks a bunch, I don't know why I didn't see that option in there. It
worked great for everything except a trigger and a view, and those
worked just fine with copying and pasting stuff. I appreciate your
help.
Oliver Garraux
Andrew J. Kelly wrote:
> There is an option in the script wizard (script for server version) to
> generate 2000 compatible code that you must check. But if you used some of
> the 2005 features you may not be able to do this completely.
> --
> Andrew J. Kelly SQL MVP

Generate 2k compatible script of 2k5 DB?

Does anyone know of a tool that will generate backwards compatible
scripts of an SQL Server 2005 database (so that it can be run on SQL
Server 2000 to create the DB and add the data)? I have views, stored
proc's, triggers, a function, and some roles that need to go with the
tables and their data as well. I've tried generating a script from SQL
Server management studio, but there are tons of errors when I try to
run it on a 2k server, even after I fix the syntax ones. There *has*
to be an easier way of doing this. I apologize if this is something
that is very well known, but this is the first time I've had to do it,
and I haven't found anything that looked like it would do it by
searching.
Thanks,
Oliver GarrauxThere is an option in the script wizard (script for server version) to
generate 2000 compatible code that you must check. But if you used some of
the 2005 features you may not be able to do this completely.
Andrew J. Kelly SQL MVP
"Oliver Garraux" <olrbengax@.gmail.com> wrote in message
news:1168220255.369875.244510@.s34g2000cwa.googlegroups.com...
> Does anyone know of a tool that will generate backwards compatible
> scripts of an SQL Server 2005 database (so that it can be run on SQL
> Server 2000 to create the DB and add the data)? I have views, stored
> proc's, triggers, a function, and some roles that need to go with the
> tables and their data as well. I've tried generating a script from SQL
> Server management studio, but there are tons of errors when I try to
> run it on a 2k server, even after I fix the syntax ones. There *has*
> to be an easier way of doing this. I apologize if this is something
> that is very well known, but this is the first time I've had to do it,
> and I haven't found anything that looked like it would do it by
> searching.
> Thanks,
> Oliver Garraux
>|||Thanks a bunch, I don't know why I didn't see that option in there. It
worked great for everything except a trigger and a view, and those
worked just fine with copying and pasting stuff. I appreciate your
help.
Oliver Garraux
Andrew J. Kelly wrote:
> There is an option in the script wizard (script for server version) to
> generate 2000 compatible code that you must check. But if you used some of
> the 2005 features you may not be able to do this completely.
> --
> Andrew J. Kelly SQL MVP