Posted by Steve on 02/07/07 15:46

On Feb 7, 7:02 am, "SQLJunkie" <vsinh...@gmail.com> wrote:
> 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

[Back to original message]