TSQL Error on the Following Statement

[vuyiswam]

Good Morning Guys

I have the Following statement, that works well when i test it alone

declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] 'MasekoTS','MasterDatabase', @Results OUT

But now, i want to include this code in a Stored Procedure and i tried to do it like this

SELECT @sql1 = 'Declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] ''' + @DB + ''', ''' + @Filename + '''@Results OUT'''
execute ( @sql1 )

and i get the Following Error

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'sp_RestoreDatabase_O_Bookin'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'sp_RestoreDatabase_O_Bookin'.

Thank you

[Alsvha]

Hi.
A good idea when you get such an error is to try and print out what you want to execute - that should help you find the unclosed quotation mark:

so "print sql1" before executing it, and then copy the statement printed into the query window and it should be easy enough to see where the quotation mark problem is located:


If you want your declare to look like the initial statement you wrote, I'd say you lack a comma before @Results OUT and you have 2 too many ' after @Results OUT.

So like this:

SELECT @sql1 = 'Declare @Results int exec [dbo].[sp_RestoreDatabase_O_Booking] ''' + @DB + ''', ''' + @Filename + ''', @Results OUT'

But try printing it out as mentioned, and it should be easy enough to find problems with quotation marks. Especially if you have set your IDE up to color strings for you.

Hope this helps.

[dglienna]

Also, plug it into SQL Mgmt and try to execute it. That used to be the trick doing Access/VB6. Access points to the SQL errors.