Post reply

dynamic SQL: Generate a variable from dynamic SQL

  • June 5, 2009 at 10:08 am

    #131634

    I'm trying to generate a script to restore about 200 databases using dynamic SQL where I pass the database name in, then the script will create the final restore statement after determining the logical file names. However, I cannot get the 2nd variable to be created.

    Any help in pointing out what I'm doing wrong will be greatly appreciated!

    --Generate a variable from dynamic SQL

    USE master;

    DECLARE @dbName VARCHAR(30)

    SELECT @dbName = 'msdb'

    DECLARE @logicalDataName VARCHAR(50),

    @logicalLogName VARCHAR(50),

    @dir1 VARCHAR(MAX),

    @dir2 VARCHAR(MAX),

    @SQL1 VARCHAR(MAX),

    @SQL2 VARCHAR(MAX),

    @SQL3 VARCHAR(MAX)

    -- Create Text for directories

    SELECT @dir1 = 'r:\rdat1\mssql\data'

    SELECT @dir2 = 'r:\rlog1\mssql\log'

    -- Create data and log file names

    SELECT @SQL1 = 'SELECT name AS @logicalDataName FROM ' +@dbName + '.dbo.sysfiles WHERE groupid = 1'

    --PRINT '@SQL1 = ' + @SQL1

    EXEC (@SQL1) --generates error on the @logicalDataName

    SELECT @SQL2 = 'SELECT name AS @logicalLogName FROM ' +@dbName + '.dbo.sysfiles WHERE groupid = 2'

    EXEC (@SQL2) --generates error on the @logicalLogName

    --PRINT '@SQL2 = ' + @SQL2

    PRINT '@logicalDataName = ' + @logicalDataName --ignores as no value

    PRINT '@logicalLogName = ' + @logicalLogName

    --Restore DB

    PRINT '--Restore database Test Script B'

    SELECT @SQL3 = 'RESTORE DATABASE ' +@dbName + ' FROM DISK = ''r:\rdat1\' + @dbName + '.bak''' + '

    WITH MOVE ''' +@logicalDataName + ''' TO ''' + @dir1 + '\' + @dbName + '_Data.mdf''' + ',

    MOVE ''' +@logicalLogName + ''' TO ''' + @dir2 + '\' + @dbName + '_Log.ldf'''

    PRINT '@SQL3 = ' + @SQL3

  • June 5, 2009 at 10:17 am

    #1005410

    Hi Clarie

    "EXEC" does not support variables within your sql, you have to work with "sp_executesql". Change your SQL variables from VARCHAR(MAX) to NVARCHAR(MAX) (required for sp_executesql) and try this:

    --Generate a variable from dynamic SQL

    DECLARE @dbName VARCHAR(30)

    SELECT @dbName = 'msdb'

    DECLARE @logicalDataName VARCHAR(50),

    @logicalLogName VARCHAR(50),

    @dir1 VARCHAR(MAX),

    @dir2 VARCHAR(MAX),

    @SQL1 NVARCHAR(MAX),

    @SQL2 NVARCHAR(MAX),

    @SQL3 NVARCHAR(MAX)

    -- Create Text for directories

    SELECT @dir1 = 'r:\rdat1\mssql\data'

    SELECT @dir2 = 'r:\rlog1\mssql\log'

    -- Create data and log file names

    SELECT @SQL1 = 'SELECT @logicalDataName = name FROM ' +@dbName + '.dbo.sysfiles WHERE groupid = 1'

    EXECUTE sp_executesql @SQL1, N'@logicalDataName VARCHAR(50) OUTPUT', @logicalDataName = @logicalDataName OUTPUT

    PRINT @logicalDataName

    Greets

    Flo

    The more I learn, the more I know what I do not know
    Blog: Things about Software Architecture, .NET development and T-SQL

    How to Post Data/Code to get the best Help How to Post Performance Problems

  • June 5, 2009 at 11:06 am

    #1005436

    Excellent solution! I'll read more on sp_executesql for future reference.

    BTW, anyone wanting to use the above script, log files are groupid = 0, not 2.

    Many thanks,

    Clarie DeWayne

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

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