Executing using sql variable does not work

hughthomas

I am banging my head against the wall (figuratively) trying to script transaction backups, and it is not working. I am concatenating together a variable to use as the single input variable to the extended stored procedure. If I print the statement to the screen and manually run it, it works perfectly. When I run it through the variable, I don't get an error but it doesn't execute the backup or restore. I even removed the concatenation and it still doesn't work. Below is the most simplified version.

I appreciate any help that you can offer.

ENVIRONMENT: Windows 2003 Server x64, SQL Server 2005 x64 SP2

SQL STATEMENT:
declare @sqls nvarchar(max)
Set @sqls = '-SQL "RESTORE LOG [MyDatabase] FROM DISK = [\\BKSERVER\SQLBACKUP\LOGSHIP\LOG_(local)_MyDatabase_*.sqb] WITH STANDBY = [\\BKSERVER\SQLBACKUP\UNDO_MyDatabase.DAT], ERASEFILES = 1, FILEOPTIONS = 1, MOVETO = [\\BKSERVER\SQLBACKUP\LOGSHIP\DONE]"'
print @sqls
exec master..sqlbackup @sqls

Jason Cook

Hi,

I've tried the statement you have supplied and I'm not able to reproduce your error. However, there are a couple of things I would suggest trying that may help:

* Change nvarchar(max) to nvarchar(4000) - SQL Backup has a limit of 64,000 characters for the command, and while you shouldn't hit that limit, defining a max variable may cause issues.

* For your @sqls set statement, you should prefix it with an N for unicode, so that it's clear what you are executing.

Also, what output do you get when the command runs? Even if the command fails to do a backup or restore, you should see two grids below - the first window titled "SQL Backup v5.2.0.2807", and the second has two columns, one called "name" and one "value".

If you don't see any output, it would be useful to run a profiler trace when executing the script to establish what is actually happening behind the scenes... that may give more insight into where the problem lies.

Thanks,
Jason

hughthomas

Thanks Jason. It was just the size of my variable. Because it is varchar rather than char, I'm not sure why that would cause the SP to not work but hey, it works now. That's what counts.

I appreciate your help.

Hugh