@errorCode OUTPUT not working as expected

ebutler · April 28, 2009 11:02AM

We are currently using Redgate SQL Backup 5 (version 5.3.0.178)
Microsoft SQL Server 2005

We have been cruising along fine using the extended SPR feature until I recently made a change to one of our maintenance plans to extend our ERASEFILES_ATSTART from 1 (1day) to 36H (36 hours).
I take full responsibility for not testing my change prior to implementing into production....
Here is my code:

DECLARE @errorCode INT
DECLARE @sqlerrorCode INT

EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE &#91;SOMEDB&#93; 
TO DISK = ''\\SOMESERVER\Backups\&lt;AUTO&gt;.sqb'' 
WITH COMPRESSION = 3, ERASEFILES_ATSTART = ''36h'', THREADCOUNT = 2, VERIFY" , @errorCode OUT, @sqlerrorCode OUT'

IF &#40;@errorCode &gt;= 500&#41; OR &#40;@sqlerrorCode &lt;&gt; 0&#41;
BEGIN
RAISERROR &#40;'SQL Backup failed with exit code: %d  SQL error code: %d', 16, 1, @errorCode, @sqlerrorcode&#41;
END

The problem with my code is that I put quotes around the 36h which was not necessary.
According to the documentation that I read, this should have failed and my RAISERROR should have kicked in because @errorCode should have returned an 850 (syntax error).
This did not happen but when I re-test it via raw sql I can see the 850 returned in the second result set of the SQLBackup extended SPR call but not in the @errorCode variable. I believe that I should have received this error and trapped the problem after the first run, instead I did not find this until after the weekend.
Questions:
Is this a bug in Redgate?
If so has it been fixed and which release can I find it?
If it is not a bug, what is the proper way to trap for syntax errors? (yeah I know...test it first

... other than testing it first!!!)? I found examples for capturing the first result set with the SINGLERESULTSET keyword but I do not see examples for isolating the second result set. Although, I would hope that I would not have to focus on capturing result sets beings that the OUTPUT variables have been put in place to avoid this kind of logic.

Thank You in advance for any help

Eddie D · April 28, 2009 2:20PM

Thank you for your post into the forum and for the Live-Chat session earlier this afternoon (28 April 2009).

I have created a Support Ticket for you, the call reference is F0021762. Hopefully we will be able to recreate the problem and offer a solution to the error.

Many Thanks
Eddie

Brian Donahue · April 29, 2009 9:52AM

Hi Eric,

The problem is that the OUTPUT variables are enclosed in a quote along with the SQL Backup command, preventing SQL Server from being able to output the variables. Since the sqlbackup stored procedure hasn't been told where to write the error code and sql error code, there is no value assigned to these variables. For instance, the following correction will get the errorcode variable to return 850:

declare @errorcode INT
declare @sqlErrorCode int
EXECUTE master..sqlbackup N'-SQL "BACKUP DATABASE &#91;21523&#93; 
TO DISK = ''c:\&lt;AUTO&gt;.sqb'' 
WITH COMPRESSION = 3, ERASEFILES_ATSTART = ''36h'', THREADCOUNT = 2, VERIFY"', @errorCode OUT, @sqlerrorCode out
select @errorcode
select @sqlerrorcode

This is not a bug in SQL Backup. I hope this clears up any confusion.

@errorCode OUTPUT not working as expected

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk