Switching from one database to another within the same script

Question

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?

Tim Lehner · Accepted Answer · 2015-01-28 14:57:31Z

1

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);

edited Jan 28, 2015 at 14:57

answered Jan 28, 2015 at 14:39

Tim Lehner

15.3k4 gold badges61 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tim Lehner Over a year ago

@user36578 you can always accept an answer if it worked to let future visitors know of the outcome.

Giorgi Nakeuri · Accepted Answer · 2015-01-28 14:56:55Z

0

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)

answered Jan 28, 2015 at 14:56

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Collectives™ on Stack Overflow

Switching from one database to another within the same script

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related