Post reply

Dynamic USE Statement in DB Creation Script

  • February 17, 2005 at 2:41 am

    #60231

    I have a script which creates the database files first. Then I want to create the structure of tables etx in the same script. I have the following SQL

    print 'Use the Framework database from now on...'

    declare @DBNAME varchar(32)

    Select @DBNAME = [Value] from #Variables where [Name] = 'DBName'

    DECLARE @sql NVARCHAR(300)

    SET @sql = 'USE ' + @DBNAME + CHAR(13)

    EXEC sp_executesql @sql

    print 'Use the Framework database from now on... done'

    GO

    My intention is to switch from the master to the new database. The new database name is stored in a temporary table with the variable @DBNAME. The Query Analyzer does not set it's path to the specified database after running this piece of script.

    I do know that sp_executesql runs in it's own batch, I tried exec(@SQL) as well, still same result.

    What am I doing wrong ?

  • February 17, 2005 at 2:50 am

    #542744

    Hi Arjen,

    You aren't doing anything 'wrong', I think you are just trying to get what you cant have.....

    You have the answer here :- "I do know that sp_executesql runs in it's own batch, I tried exec(@SQL) as well, still same result." - sp_executesql generates and runs in its own spid and is only 'Live' during the lifetime of this spid. Once it has executed and you get to the next line of code, sp_executesql (and exec(@sql)) has gone out of scope.

    I am fairly certain you will need to look for a different way of achieving this.

    Over to the experts now.

    Have fun

    Steve

    We need men who can dream of things that never were.

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

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