Dynamic query output is different than its static query.

Hrishikesh Lele 1

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.

Hrishikesh Lele 1 Reputation point

2023-04-06T09:18:25.16+00:00

PFA the snapshot. This picture shows the output from the dynamic query as well the static query.

The stored procedure is as below -

I have checked the relevant security permissions in the SSMS software. Request please let me know the cause and solution to this issue.
Viorel 114.7K Reputation points

2023-04-06T09:30:35.58+00:00

Maybe you should include the USE statement into @sql1: set @sql1 = N"USE master alter database....
Hrishikesh Lele 1 Reputation point

2023-04-06T09:42:50.8833333+00:00

That did not work sir. Hence, seperated it to two different variables.
Olaf Helper 43,246 Reputation points

2023-04-06T11:20:31.01+00:00

This picture shows the output from the dynamic query as well the static query

The screenshot (photos) are unreadable. Please post error message and your code as text.

Hrishikesh Lele 1

/************ Stored Procedure ******************/


USE [FTV118]
GO
/****** Object:  StoredProcedure [dbo].[RestoreDb_CO]    Script Date: 06/04/2023 17:02:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[RestoreDb_CO]
@BackupPath varchar(200)

as

set nocount on

declare
@DbName varchar(15),
@Query nvarchar(200),
@Query1 nvarchar(200),
@sql nvarchar(500),
@sql1 nvarchar(500)


set @DbName = left(Right(@BackupPath, charindex('\', reverse(@BackupPath))-1), charindex('_', right(@BackupPath, charindex('\', reverse(@BackupPath))-1))-1)

print @Dbname


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

set @sql1 = N'alter database [' +@DbName +'] ' + N'set SINGLE_USER with rollback immediate


Restore database ' + 
@Dbname +  N' 
from disk = ''' + @BackupPath + N''' with replace, recovery;

alter database [' +@DbName +'] ' + N'set MULTI_USER'

print(@sql1)
exec(@sql1)




/************** Executing Stored Procedure ***************/


USE [FTV118]
GO

DECLARE	@return_value int

EXEC	@return_value = [dbo].[RestoreDb_CO]
		@BackupPath = N'E:\Backup\Database\FTV118_202345_141 49.BAK'

SELECT	'Return Value' = @return_value

GO




/************** My Stored Procedure Output **********/

FTV118
USE master;
alter database [FTV118] set SINGLE_USER with rollback immediate


Restore database FTV118 
from disk = 'E:\Backup\Database\FTV118_202345_141 49.BAK' with replace, recovery;

alter database [FTV118] set MULTI_USER
Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 3102, Level 16, State 1, Line 6
RESTORE cannot process database 'FTV118' because it is in use by this session. It is recommended that the master database be used when performing this operation.
Msg 3013, Level 16, State 1, Line 6
RESTORE DATABASE is terminating abnormally.

(1 row affected)

Completion time: 2023-04-06T17:02:36.7335064+05:30



/******************** This is a set of sql statements that are designed to run dynamically using a stored procedure. Executing them to test if the query is okay *************/


USE master;
alter database [FTV118] set SINGLE_USER with rollback immediate


Restore database FTV118 
from disk = 'E:\Backup\Database\FTV118_202345_141 49.BAK' with replace, recovery;

alter database [FTV118] set MULTI_USER





/**************** Output for the above query ****************/


Nonqualified transactions are being rolled back. Estimated rollback completion: 0%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Processed 576 pages for database 'FTV118', file 'GLF14' on file 1.
Processed 1 pages for database 'FTV118', file 'GLF14_log' on file 1.
RESTORE DATABASE successfully processed 577 pages in 0.007 seconds (643.066 MB/sec).

Completion time: 2023-04-06T17:03:36.2920290+05:30

Hrishikesh Lele 1 Reputation point

2023-04-06T11:52:39.1066667+00:00

Sorry for inconvenience. I have shared the statements in text format with comments. Please let me know if anything else is required.
Viorel 114.7K Reputation points

2023-04-06T13:47:39.4633333+00:00

Maybe you should define this stored procedure inside another (or master) database?

2 answers

Erland Sommarskog 107.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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
LiHongMSFT-4306 25,651 Reputation points

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.
Please sign in to rate this answer.
Hrishikesh Lele 1 Reputation point

2023-04-07T08:36:21.0766667+00:00

Will try both the thoughts and come back to you.

LiHongMSFT-4306 25,651 Reputation points

2023-04-10T05:59:15.1666667+00:00

Hi @Hrishikesh Lele Is there any update on this issue?

Hrishikesh Lele 1 Reputation point

2023-04-11T02:25:05.9733333+00:00

Hi, I will be able to implement the solutions on the machine in this week. Surely will update on the same.
Sign in to comment

Share via

Dynamic query output is different than its static query.

2 answers