CODEWITHSUNDEEP
sql-servert-sql
user36578
user36578

Reputation: 55

Switching from one database to another within the same script

I would like to know how I can switch from one database to another within the same script. I have a script that reads the header information from a SQL Server .BAK file and loads the information into a test database. Once the information is in the temp table (Test database) I run the following script to get the database name.

This part works fine. 

INSERT INTO @HeaderInfo EXEC('RESTORE HEADERONLY 
FROM DISK = N''I:\TEST\database.bak''
WITH NOUNLOAD')

DECLARE @databasename varchar(128);
SET @databasename = (SELECT DatabaseName FROM @HeaderInfo);

The problem is when I try to run the following script nothing happens. The new database is never selected and the script is still on the test database. 

EXEC ('USE '+ @databasename)

The goal is switch to the new database (USE NewDatabase) so that the other part of my script (DBCC CHECKDB) can run. This script checks the integrity of the database and saves the results to a temp table.

What am I doing wrong?

Upvotes: 1

Views: 1505

Answers (2)

Tim Lehner
Tim Lehner

Reputation: 15251

You can't expect a use statement to work in this fashion using dynamic SQL. Dynamic SQL is run in its own context, so as soon as it has executed, you're back to your original context. This means that you'd have to include your SQL statements in the same dynamic SQL execution, such as:

declare @db sysname = 'tempdb';
exec ('use ' + @db + '; dbcc checkdb;')

You can alternatively use fully qualified names for your DB objects and specify the database name in your dbcc command, even with a variable, as in:

declare @db sysname = 'tempdb';
dbcc checkdb (@db);

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

You can't do this because Exec scope is limited to dynamic query. When exec ends context is returned to original state. But context changes in Exec itself. So you should do your thing in one big dynamic statement like:

DECLARE @str NVARCHAR(MAX)

SET @str = 'select * from table1
USE DatabaseName
select * from table2'

EXEC (@str)

Upvotes: 0

Related Questions