Post reply

Switching from one database to another within the same script.

  • January 28, 2015 at 7:16 am

    #294278

    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 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?:unsure:

  • January 28, 2015 at 7:58 am

    #1773694

    the issue is scope: the use statmetn is valid only for the contents of the EXEC command.

    ALL the statements have to be in the full exec command; once it finishes, it's back to the current context.

    EXEC ('USE '+ @databasename;

    SELECT db_name(),@@Version;

    INSERT INTO ATableInTheOtherDatabase(ColumnList) SELECT '1,''Bananas'';')

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 29, 2015 at 6:07 am

    #1773957

    Thanks that worked.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply