Dynamic SQL does not work!

[SQLJunkie]

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?

Here is the code I am using:
----- EXECUTE SQL Statement on all Databases

DECLARE
@DatabaseName varchar(100)
, @SQL varchar(500)

DECLARE DBNameCursor CURSOR FOR
SELECT
[Name] AS DatabaseName
FROM
master.dbo.sysdatabases
ORDER BY
DatabaseName

OPEN DBNameCursor

FETCH NEXT FROM DBNameCursor
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
----- Change to Database
SELECT
@SQL = 'USE ' + @DatabaseName

EXEC(@SQL)

----- SQL Statement to be run
EXEC dbo.SPName

FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
END

CLOSE DBNameCursor
DEALLOCATE DBNameCursor
----------------------
Thanks in advance!
Vishal Sinha

[Steve]

On Feb 7, 7:02 am, "SQLJunkie" <[e-mail]:

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?

Here is the code I am using:
----- EXECUTE SQL Statement on all Databases

DECLARE
@DatabaseName varchar(100)
, @SQL varchar(500)

DECLARE DBNameCursor CURSOR FOR
SELECT
[Name] AS DatabaseName
FROM
master.dbo.sysdatabases
ORDER BY
DatabaseName

OPEN DBNameCursor

FETCH NEXT FROM DBNameCursor
INTO @DatabaseName

WHILE @@FETCH_STATUS = 0
BEGIN
----- Change to Database
SELECT
@SQL = 'USE ' + @DatabaseName

EXEC(@SQL)

----- SQL Statement to be run
EXEC dbo.SPName

FETCH NEXT FROM DBNameCursor
INTO @DatabaseName
END

CLOSE DBNameCursor
DEALLOCATE DBNameCursor

----------------------
Thanks in advance!
Vishal Sinha

If I recall correctly EXEC starts a new thread that won't know about
the preceeding USE statement.

My favorite dynamic SQL site:
http://www.sommarskog.se/dyn-search.html

[Erland Sommarskog]

SQLJunkie (vs******@gmail.com) writes:

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?

Yes, but the effect of the USE lasts only for the duration of the of the
dynamic SQL.

SELECT
@SQL = 'USE ' + @DatabaseName

EXEC(@SQL)

----- SQL Statement to be run
EXEC dbo.SPName

If all you want to do is to run a stored procedure in each database,
this is the easiest way to do:

SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname

You may also be interested in sp_MSforeachdb:

EXEC sp_MSforeachdb 'EXEC ?.dbo.SPNAme'

This procedure is undocumented and unsupported, but it's nevertheless
fairly popular.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[SQLJunkie]

Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname

But what if I have to run SPs like SP_updatestats etc? then it will
not work.

Thanks!

On Feb 7, 5:35 pm, Erland Sommarskog <[e-mail]:

SQLJunkie ([e-mail]) writes:

I have a small SQL script that rotates through all databases on the
server and executes a Stored Procedure in each of them. Here are the
steps:
1: The first step is to get name of databases in to a cursor.
2: Build dynamic SQL to change database to database in variable - this
step fails
3: Execute SP
4: Move to next database.
Is it not possible to change current database using Dynamic SQL? Can
someone help me here please?

Yes, but the effect of the USE lasts only for the duration of the of the
dynamic SQL.

SELECT
@SQL = 'USE ' + @DatabaseName

EXEC(@SQL)

----- SQL Statement to be run
EXEC dbo.SPName

If all you want to do is to run a stored procedure in each database,
this is the easiest way to do:

SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname

You may also be interested in sp_MSforeachdb:

EXEC sp_MSforeachdb 'EXEC ?.dbo.SPNAme'

This procedure is undocumented and unsupported, but it's nevertheless
fairly popular.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

SQLJunkie (vs******@gmail.com) writes:

Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname

But what if I have to run SPs like SP_updatestats etc? then it will
not work.

Au contraire, it will work just fine! If you say:

EXEC mydb..sp_systemprocedure

the system procedure will execute in the context of mydb.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[SQLJunkie]

Thanks - it worked!

Momentary lapse of reason :)

On Feb 8, 5:39 pm, Erland Sommarskog <[e-mail]:

SQLJunkie ([e-mail]) writes:

Thanks everyone for the replies. The following example is fine for
generic SPs:
SELECT @SPname = @DatabaseName + '.dbo.SPName'
EXEC @SPname

But what if I have to run SPs like SP_updatestats etc? then it will
not work.

Au contraire, it will work just fine! If you say:

EXEC mydb..sp_systemprocedure

the system procedure will execute in the context of mydb.

--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx