Dynamic query output is different than its static query.

Hrishikesh Lele 1 Reputation point
2023-04-06T09:06:25.04+00:00

Hi, I am trying to restore an in-use database from disk using a stored procedure. The procedure output after execution is attached below. From the attachment, it is seen that when the query is run statically, it is successfully run, with no error. When the same query is written dynamically (as in the stored procedure…), the Management Studio gives an error. Error info is also attached. We want the stored procedure to execute successfully without any error and restore the in-use database. Request please assist in troubleshooting the same.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,869 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Erland Sommarskog 102.2K Reputation points
    2023-04-06T21:44:32.33+00:00

    The problem is that you run USE master in a separate batch of dynamic SQL:

    set @sql = N'USE master;'
    exec(@sql)
    print(@sql)
    
    

    But that USE statement only has effect with that EXEC. If you add

    PRINT db_name()
    

    to the above, you will see that you are in the same database as before. But the concept as such is a little scary. You running in a stored procedure in a database and then you try to restore that database. Which means that the stored procedure may no longer exist when the RESTORE completes. What actually happens then, I don't know.

    0 comments No comments

  2. CosmogHong-MSFT 23,561 Reputation points Microsoft Vendor
    2023-04-07T07:09:03.33+00:00

    Hi @Hrishikesh Lele Try something like this:

    DECLARE 
    @exec NVARCHAR(100) = N'master.sys.sp_executesql',
    @DbName varchar(15),
    @Query nvarchar(200),
    @Query1 nvarchar(200),
    @sql nvarchar(500),
    @sql1 nvarchar(500)
    
    SET @sql1 = N'alter database [' +@DbName +'] ' + N'set SINGLE_USER with rollback immediate......'
    EXEC @exec @sql1;
    

    Best regards,

    Cosmog Hong


    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.