@errorCode OUTPUT not working as expected
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 [SOMEDB] TO DISK = ''\\SOMESERVER\Backups\<AUTO>.sqb'' WITH COMPRESSION = 3, ERASEFILES_ATSTART = ''36h'', THREADCOUNT = 2, VERIFY" , @errorCode OUT, @sqlerrorCode OUT' IF (@errorCode >= 500) OR (@sqlerrorCode <> 0) BEGIN RAISERROR ('SQL Backup failed with exit code: %d SQL error code: %d', 16, 1, @errorCode, @sqlerrorcode) 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.
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