Post reply

Dynamic Sql and sp_executesql - Need help returning values

  • March 13, 2006 at 9:52 am

    #170926

    Very confused. Just when I think I understand constructing some of these statements, I run into a head scratcher.  I did something very similar in propagating the contents of a table in an earlier part of this task.  What I am trying to do now is to read the contents of a table (BackupFileData) and use the contents to construct restore statements that will be run on another SQL server.  I have reduced the contents of this script down just for testing.

    The problem is that after the EXEC sp_executesql statement (which returns the correct value) and cannot use the value to construct the next sql statement.  The value "@logicaldbname" only contains NULL.  So when I try to create @sql2, it is of course incorrect.

    use master

    go

    declare @cmd1    nvarchar(4000)

    declare @retType   nvarchar(50)

    declare @userdb    nvarchar(50)

    declare @logicaldbname  nvarchar(50)

    declare @RestorePath  nvarchar(128)

    declare @sql1    nvarchar(128)

    declare @sql2    nvarchar(128)

    declare userdb_list cursor for

     select dbname from BackupFileData

     where dbname not in ('master','msdb','tempdb','model','pubs')

    open userdb_list

    fetch userdb_list into @userdb

    while @@fetch_status = 0

    begin

     set @RestorePath = 'C:\Migration\'

     -- Retrieve information from previous backup to construct restore script

     set @retType =  N'@retVal nvarchar(50) OUTPUT'

     set @cmd1 = N'select db_logical_dbname from BackupFileData where dbname = ''' + @userdb + ''''

     

     -- The following returns NorthWind

     exec sp_executesql @cmd1, @retType, @logicaldbname OUTPUT

     

     -- Construct SQL statements for restore

     set @sql1 = 'restore database ' + @userdb

     set @sql2 = 'from disk = ' + @RestorePath + @logicaldbname

     select @sql2  -- Returns NULL/Value should be "Northwind"

     fetch userdb_list into @userdb

    end

    close userdb_list

    deallocate userdb_list

    go

    Thank you,

    Mark

  • March 13, 2006 at 10:13 am

    #626300

    Change this line:

    ...

     set @cmd1 = N'select @retVal = db_logical_dbname from BackupFileData where dbname = ''' + @userdb + ''''


    * Noel

  • March 13, 2006 at 1:27 pm

    #626361

    Thank you very much!

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

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