SQL Execute Task syntax problem

Question

Post reply

SQL Execute Task syntax problem

Matthew Cushing

SSCrazy

Points: 2898
More actions
October 17, 2011 at 8:43 am

#150605

I have a command I have to throw to SQL from SSIS, we're using Red-Gate to apply log files to a backup readonly db.
DECLARE @p_filename varchar(200)
set @p_filename = 'filename.SQB'
EXECUTE master..sqlbackup '-SQL "RESTORE LOG [PCC-RRDB-CHG] FROM DISK = ''\\BI01\PCCLogFiles\'+ @p_filename +' WITH STANDBY = ''G:\PCCBackupDONOTREMOVE\Undo_projects.dat'',PASSWORD = ''password'', DISCONNECT_EXISTING"'
It's bitching about the + near @p_filename, I know it's a question of single or double quotes. The only double quotes I have are before RESTORE and after DISCONNECT_EXISTING.
I know it works because when I just put in the filename instead of passing it, it works fine.
The other question I have is how can I check the return code? If it's a specific number, I want the task to fail.

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

just to check., are you writing this code as an expression or are you entering it into SQLstatement in the editor?

to get the return code change the resultset to single row and then in parameter mapping map this to a variable.

you can then check this variable through a precedence constraint to handle the error

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 2

Right now I am just trying to execute it in a sql edit window. I plan on taking it and putting it into a SQL task in an SSIS job.

I'll check into setting it to single row.

Any ideas on why the syntax would blow up on me when I try to use the parameter?

John Morehouse SSC-Addicted Points: 445 More actions · Answer 3

This worked for me:

DECLARE @p_filename varchar(200)

set @p_filename = 'filename.SQB'

declare @sql varchar(500) = '-SQL "RESTORE LOG [PCC-RRDB-CHG] FROM DISK = ''"\\BI01\PCCLogFiles\' + @p_filename +'" WITH STANDBY = ''G:\PCCBackupDONOTREMOVE\Undo_projects.dat'',PASSWORD = ''password'', DISCONNECT_EXISTING"'

select @sql

I think that you were missing a ' " ' next to the file name.

Hope this helps!

James Billings, Redgate SSChasing Mays Points: 602 More actions · Answer 4

I think the issue is that SQL Backups' procedure is seeing the first single quote as the end of the command, and the "+" is then unexpected. You can get around it by parameterizing the whole command as below (note you need a couple of extra '' before "WITH STANDBY" from your original script

DECLARE @p_filename varchar(200)

set @p_filename = 'filename.SQB'

DECLARE @cmd VARCHAR(MAX)

SET @cmd = '-SQL "RESTORE LOG [PCC-RRDB-CHG] FROM DISK = ''e:et1\' + @p_filename + ''' WITH STANDBY = ''E:\PCCBackupDONOTREMOVE\Undo_projects.dat'',PASSWORD = ''password'', DISCONNECT_EXISTING"'

EXECUTE master..sqlbackup @cmd

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 5

When I execute the procedure (which is now working thanks to James and SQLRUs), I get two rows back in my sql window. One is an exitcode, the other is a sqlerrorcode. If I am doing as you suggest and returning is as a parameter to check, which code will it get?

The sqlerrorcode is the one I want.

James Billings, Redgate SSChasing Mays Points: 602 More actions · Answer 6

I think the problem here is that the SQL Backup procedure returns multiple resultsets which I don't think you can easily access via simple SQL commands (although someone more knowledgeable than myself may know of a way). I did find this article where usage of some CLR code was suggested:

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 7

Honestly, I think it might even be enough that if anything is returned, it's probably an error so we can have the job fail at that point.

unless anyone has another suggestion?

steveb. SSC-Forever Points: 46733 More actions · Answer 8

are you still planning to run this in ssis?

If so you could pass the results of the procedure to an object, and then loop through this object until you find the error code and then handle the error based on this.

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 9

Steve,

Yes, that's the plan. Right now, if the exec stmt fails and the task fails, that's good enough, but in truth, there are several error messages that could come back, including one that isn't really an error.

I'll give that some thought.

Any idea if there's somewhere to post a walkthrough of the procedure with screenshots to get feedback on it?

steveb. SSC-Forever Points: 46733 More actions · Answer 10

Not too sure about where to post screenshots, maybe attach them to your post.

Doing this in SSIS would be fairly straight-foward and you could use an expression to extract only the error message you are interested in.

Matthew Cushing SSCrazy Points: 2898 More actions · Answer 11

Ergh!

1) created a variable to hold the whole command string:

"DECLARE @cmd VARCHAR(MAX);

set @cmd = '-SQL \"restore LOG [PCC-RRDB-CHG] FROM DISK = ''\\\\BI01\\PccLogFiles\\+ @[User::v_fileName] + ''' WITH STANDBY = ''G:\\PCCBackupDONOTREMOVE\\Undo_projects.dat'',PASSWORD = ''password'', DISCONNECT_EXISTING\"';

EXECUTE master..sqlbackup @cmd;"

2) the @user variable isn't getting evalutated, so I put " in before the first plus and after the second, and that evaulated fine in a script task, but running it in a sql task gave me an unclosed quote problem.

the @user::v_fileName is coming from a forloop.

Is it possible to run the command as a script task instead of a sql command? I have to run that command against a db.