Jul 3, 2014 at 6:14 PM
Edited Jul 3, 2014 at 9:39 PM
Has anyone tried the stored procedure with the debug flag set to 1? I get errors with the debug flag set, but not when it is reset. E.g. executing a log backup on a database: "Msg 102, Incorrect syntax near 'BEGI'. Level 15, State 1, Procedure expressmaint,
Line 525" (the line number might be a little different) The error seems to be on "IF @debug = 1 PRINT 'LOG BACKUP : ' +
Stepping through it with the debugger I see that the debugger has highlighted the following as a single statement:
SET @execmd = N'BACKUP LOG [' + @database + '] TO DISK = ''' + @backupfilename + '''' +
CASE WHEN @backupwith IS NULL THEN '' ELSE (' WITH ' + @backupwith) END
--IF @debug = 1 PRINT 'LOG BACKUP : <some command>'-- + @execmd
It's as if it isn't parsing the text of the stored procedure correctly.
This is not the only OP value that has an error when debug is on (1). Everything seems to work when it's off (0).
I get the feeling that we fixed this issue once, but alas I cannot find out where it might have been documented. The fix might have had something to do with using SET QUOTED_IDENTIFIER ON. Or something similar. Or not.
I'm using SQL Server 2012 Express on Windows 7. My testing is through the SSMS, but often we run them from the command line via sqlcmd, with the same results.
Any help here would be appreciated.
EDIT 2014-Jul-03 16:22:
I've narrowed it down to the "SET DATEFORMAT YMD" statement near the beginning of the script. If I comment out this line it works without error. I'm not sure why...