dynamically switching databases in a script

I've got a situation where I need to execute portions of a script against every database on a given instance. I don't know the name of all the databases beforehand so I need to scroll through them all and call the "use" command appropriately.

I need the correct syntax, the following won't work:

DECLARE DBS CURSOR FOR
SELECT dbname
FROM #helpdb
ORDER BY dbname

OPEN DBS

FETCH NEXT
FROM DBS
INTO
@.dbname

WHILE @.@.FETCH_STATUS = 0
BEGIN

USE @.dbname

The last line - the "USE" statement - is invalid. The following for example works:

USE master

But when supplied a declared variable a syntax error results for the use command because it expects an identifier.

So .. what is the correct syntax to pass a declared parameter to "USE", or is there another way to meet this requirement?

Thanks for your time.

This is not possible right now since you cannot use variables in lot of statements in place of options or identifiers. You can use dynamic SQL though and below is the easiest way to do it:

declare @.sp nvarchar(500)

...

while .....

begin

-- use dbo.sp_executesql for SQL Server 2000

set @.sp = quotename(@.dbname) + N'sys.sp_executesql'

exec @.sp N'your sql string that needs to execute against db'

...

end

|||although this allowed the "use .." statement to run, it didn't have the effect I need. The remainder of the script was still running in the context of the original database.|||

So here's what I really need:

I need a way to switch the context of a script from one database to another, where I do not know the name of the databases beforehand (so they can't be hard-coded).

|||What I suggested will work provided the code that you want to run within context of the database is executed dynamically. Another approach is to pre-process the script file based on the database name and then run it. With SQL Server 2005, you can do this using SQLCMD pre-processing features.

dynamically delete data

Hi All,
I have the following situation.
Every month, I populate data from a source table.
This table has a field called process_date (char data type) and the
format is mmyy. So, 0406 means data for the month of April of 2006.
This source table always overlaps with old data. For example, for this
month it may have data for January, February or March of 2006, which I
already have processed.
What I do presently is I manually run a delete command and then insert
in the target table.
Such as:
delete Table1 where Process_Date<>'0406'
I want to make this automated so that I will not have to manually run
the above code.
I was wondering how could I achieve that?
I will highly appreciate your help.
Thanks a million in advance.
Best regards,
MamunHello Mamun,
You could create a SQL Server Agent job to run every month. This job
can execute the T-SQL statements you require to insert/delete the
required data and won't require any intervention by you (although you
should be checking that whenever the job executes it executes
successfully).
If you're new to creating SQL Server Agent jobs then SQL Server Books
Online should be able to run you through the process.
HTH,
Nate.
mamun wrote:
> Hi All,
> I have the following situation.
> Every month, I populate data from a source table.
> This table has a field called process_date (char data type) and the
> format is mmyy. So, 0406 means data for the month of April of 2006.
> This source table always overlaps with old data. For example, for this
> month it may have data for January, February or March of 2006, which I
> already have processed.
> What I do presently is I manually run a delete command and then insert
> in the target table.
> Such as:
> delete Table1 where Process_Date<>'0406'
> I want to make this automated so that I will not have to manually run
> the above code.
> I was wondering how could I achieve that?
> I will highly appreciate your help.
> Thanks a million in advance.
> Best regards,
> Mamun

Dynamically creating variable names

Ok, here it goes. I'll try and explain my situation as best as I can.
I would like to dynamically create variable names. We store our data in
monthly partitioned tables.......table_200501, table_200502...
I am creating a view that is a union of all of the partitioned tables. So
in order to do this I am creating a cursor that selects all tables from
information_schema where table_name like 'table_%'
Then once I have the table name I am creating a varchar that does my select
from the table that is passed into the cursor variable. In the end I will
have a varchar that looks like this
select....................from table_200501
union all
select...................from table_200502
Over time the varchar that I have created will run out of space since it can
only hold 8000 characters. So once the size of the varchar gets near 8000 I
would like to create a new variable named sqlstringn.........with n being
the next number available.
So just for sample I tried doing this but it wont work
declare @.counter int
set @.counter = 5
declare @.SQLString + @.counter as varchar(8000)
Obviously this doesn't work, but is there any other way to dynamically
create variable names?
Any help or suggestions are very much appreciated.
ThanksI did that once (dynamically creating variables), and it required creating
dynamic SQL within dynamic SQL.
You probably don't want that in a production application.
What you can do is to allocate, say three, variables and concatenate them on
execution. (ie: Execute (@.Var1 + @.Var2 + @.Var3) I know you are doing this
in a cursor, but the techinque should be the same.
I don't mean to stray, but is it possible to modify your design from
horizontal to vertical so you don't have to deal with so many tables?
"Andy" wrote:

> Ok, here it goes. I'll try and explain my situation as best as I can.
> I would like to dynamically create variable names. We store our data in
> monthly partitioned tables.......table_200501, table_200502...
> I am creating a view that is a union of all of the partitioned tables. So
> in order to do this I am creating a cursor that selects all tables from
> information_schema where table_name like 'table_%'
> Then once I have the table name I am creating a varchar that does my selec
t
> from the table that is passed into the cursor variable. In the end I will
> have a varchar that looks like this
> select....................from table_200501
> union all
> select...................from table_200502
> Over time the varchar that I have created will run out of space since it c
an
> only hold 8000 characters. So once the size of the varchar gets near 8000
I
> would like to create a new variable named sqlstringn.........with n bei
ng
> the next number available.
> So just for sample I tried doing this but it wont work
> declare @.counter int
> set @.counter = 5
> declare @.SQLString + @.counter as varchar(8000)
> Obviously this doesn't work, but is there any other way to dynamically
> create variable names?
> Any help or suggestions are very much appreciated.
> Thanks
>

Dynamic WHERE clause (SQL server 2005)

I'm in a situation where a user should be able to choose what data to
retrieve from a table. The criteria is not constant, sometime it is included
,
other times not. The problem I'm facing is how to create a dynamic WHERE
clause. I would prefere to avoid client side embedded SQL.
My first idea was to create a table valued functions that takes the criteria
and using dynamic SQL returns a table with the subset of data. The reason I
chose a TVF is so I can call it from multiple stored procedures.
The following function is accepted by SQL server, but when I run against it
I get the following error 'Only functions and extended stored procedures can
be executed from within a function.'
ALTER FUNCTION [dbo].[GetTagIds2]
(
@.TagMask NVARCHAR(50)
)
RETURNS @.ResultTable TABLE
(
TagId INT PRIMARY KEY NOT NULL,
Name NVARCHAR(100) NOT NULL
)
AS
BEGIN
DECLARE @.DynSql VARCHAR(1024);
-- this works fine
-- INSERT INTO @.ResultTable(TagId,Name)
-- SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag
-- WHERE IMS_Tag.Name LIKE(@.TagMask)
SET @.DynSql = @.DynSql + 'INSERT INTO @.ResultTable(TagId,Name)';
SET @.DynSql = @.DynSql + 'SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag';
SET @.DynSql = @.DynSql + 'WHERE IMS_Tag.Name LIKE(@.TagMask)';
EXEC sp_executesql @.DynSql;
RETURN
END
Any idea why this is not working?
If there are any better ways of doing this please let me know.You cannot use dynamic SQL in a function; you will need to use a different
approach.
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/share_data.html
"Christopher Kimbell" <c_kimbell@.newsgroup.nospam> wrote in message
news:0BB5E543-76BC-4EC7-B038-DF0E9308D3DC@.microsoft.com...
> I'm in a situation where a user should be able to choose what data to
> retrieve from a table. The criteria is not constant, sometime it is
> included,
> other times not. The problem I'm facing is how to create a dynamic WHERE
> clause. I would prefere to avoid client side embedded SQL.
> My first idea was to create a table valued functions that takes the
> criteria
> and using dynamic SQL returns a table with the subset of data. The reason
> I
> chose a TVF is so I can call it from multiple stored procedures.
> The following function is accepted by SQL server, but when I run against
> it
> I get the following error 'Only functions and extended stored procedures
> can
> be executed from within a function.'
>
> ALTER FUNCTION [dbo].[GetTagIds2]
> (
> @.TagMask NVARCHAR(50)
> )
> RETURNS @.ResultTable TABLE
> (
> TagId INT PRIMARY KEY NOT NULL,
> Name NVARCHAR(100) NOT NULL
> )
> AS
> BEGIN
> DECLARE @.DynSql VARCHAR(1024);
> -- this works fine
> -- INSERT INTO @.ResultTable(TagId,Name)
> -- SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag
> -- WHERE IMS_Tag.Name LIKE(@.TagMask)
> SET @.DynSql = @.DynSql + 'INSERT INTO @.ResultTable(TagId,Name)';
> SET @.DynSql = @.DynSql + 'SELECT IMS_Tag.TagId, IMS_Tag.Name FROM IMS_Tag';
> SET @.DynSql = @.DynSql + 'WHERE IMS_Tag.Name LIKE(@.TagMask)';
> EXEC sp_executesql @.DynSql;
> RETURN
> END
> Any idea why this is not working?
> If there are any better ways of doing this please let me know.|||Christopher Kimbell wrote:
> I'm in a situation where a user should be able to choose what data to
> retrieve from a table. The criteria is not constant, sometime it is
> included, other times not. The problem I'm facing is how to create a
> dynamic WHERE clause. I would prefere to avoid client side embedded
> SQL.
See Erland's article on this topic here:
http://www.sommarskog.se/index.html
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks guys!
The information was very usefull.

Dynamic SqlDataSource

Ok, here's my situation.

I have a dynamic page that accepts a "type" query string. This type query string is the name of a table I want to display on the page in the GridView. Creating a different SqlDataSource/Strongly typed class for every type isn't possible because I need to make this flexible for future updates. So therefor I basically need to be able to Sort, Page, Edit and Delete with the Grid View without knowing the table name at compile-time.

Any help is appreciated,

Thanks!

Displaying the table contents is relatively straightforward. In your code-behind, you just create a SelectCommand of SELECT * FROM " + Request.QueryString["table"]. Of course, you would have to verify that the contents of the querystring is a valid value. The bigger problem is with the update and delete commands. You could create stored procedures for all tables, then fetch the names of the appropriate ones from a database depending on the value of "table" in the querystring. But that isn't particularly flexible. An alternative is toprogrammatically examine the selected table's schema, and dynamically construct update and delete commands based on the column names.|||Thanks for your response. I actually found a way to dynamically get the data itself to enable paging/sorting. I did this just by creating a stored procedure called GetTable with a @.table parameter. So this works well. As for updating and deleting I'll look into that, thanks.|||At least you are using Stored Procedures. Kewl!