Transact-SQL for Restore Filelistonly using a variable

[spattag]

I am trying to script the process RESTORE FILELISTONLY using an Exec()
statement. I cant seem to get it to work with a variable. Here is
what I have.

Declare @Sql varchar(100)
Declare @File varchar(100)
Select @File = 'C:\atest\RO_Backup.bak'
SELECT @Sql = ''''+ 'RESTORE FILELISTONLY FROM DISK=''' + '''' + @File
+ '''' + '''' + ''''
Exec (@Sql)

The above gives me an error: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'RESTORE FILELISTONLY FROM DISK='C:\atest
\RO_Backup.bak''.

However, the following DOES work:

Exec ('RESTORE FILELISTONLY FROM DISK=''c:\atest\RO_Backup.bak''')

How can i get it to work with a variable in the Exec statement? If i
do a print statement for the @Sql variable, all the 'quotes' are in
the rite place and quantity as in the above Exec statement, but it
wont work.

Thanks!!

Greg

[Erland Sommarskog]

(sp*****@gmail.com) writes:

I am trying to script the process RESTORE FILELISTONLY using an Exec()
statement. I cant seem to get it to work with a variable. Here is
what I have.

Declare @Sql varchar(100)
Declare @File varchar(100)
Select @File = 'C:\atest\RO_Backup.bak'
SELECT @Sql = ''''+ 'RESTORE FILELISTONLY FROM DISK=''' + '''' + @File
+ '''' + '''' + ''''
Exec (@Sql)

The above gives me an error: Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'RESTORE FILELISTONLY FROM DISK='C:\atest
\RO_Backup.bak''.

Make that a rule: when your dynamic SQL does not work out, add:

PRINT @Sql

Once you see the SQL statement, it may be all so apparent to you.

In this case the problem is that your SQL statement is a single
string literal which is not legal SQL. You should remove at least the
first and last set of ''''. Probably more, but you'll find out when
you add your debug PRINT.

Then again, why not try:

RESTORE FILELISTONLY FROM DISK=@File

Since a variable is permitted for the filename, why use dynamic SQL at
all?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[spattag]

>

Make that a rule: when your dynamic SQL does not work out, add:

PRINT @Sql

Once you see the SQL statement, it may be all so apparent to you.

In this case the problem is that your SQL statement is a single
string literal which is not legal SQL. You should remove at least the
first and last set of ''''. Probably more, but you'll find out when
you add your debug PRINT.

Then again, why not try:

RESTORE FILELISTONLY FROM DISK=@File

Since a variable is permitted for the filename, why use dynamic SQL at
all?
--
Erland Sommarskog, SQL Server MVP, [e-mail]

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Ok thanks for your help. The reason I was using it in an Exec()
statement is because i was using it with an INSERT and storing the
results in a temp table.
After reading your post I tried Exec('Restore Filelistonly From Disk='
+ '' + @Sql + '') which worked great.
What also worked was Exec('' + @Sql + ''). Like you said, the fact
that it was a single string leteral was messing me up.

Thanks for your help!

Greg