Post reply

Combining EXEC() and a USE command

  • February 12, 2009 at 3:58 pm

    #206504

    Greetings all. I'm trying to programatically change the active database from within a WHILE loop. When I run the following statement, no errors are generated but the active database does not change:

    USE master;

    DECLARE@dbName VARCHAR(25);

    SET @dbName = 'myDB';

    EXEC ('USE ' + @dbName);

    SELECT name FROM sys.database_files WHERE type = 1;

    I tried replacing the EXEC with EXECUTE and even sp_executesql but of course that didn't help. I'm rather new to the world of T-SQL programming so it is entirely possible that I'm doing something wrong.

    PLEASE, any assistance or advice will be greatly appreciated. TIA!

  • February 12, 2009 at 5:04 pm

    #943281

    [font="Verdana"]The issue here is that as soon as the exec() finishes, the database you are in will revert. The use only applies to the scope of the exec().

    Try this instead:

    use master;

    declare @dbName sysname;

    set @dbName = 'myDB';

    exec ('use [' + @dbName + ']; select name from sys.database_files where type = 1;');

    [/font]

  • February 12, 2009 at 5:12 pm

    #943290

    I'd recommend Bruce's approach, though keep in mind that you have a limit of 8kb to send through stuff in the batch.

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

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