PRINT error with @debug=1

Jul 3, 2014 at 5:14 PM
Edited Jul 3, 2014 at 8: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 : ' + @execmd".

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...
Jul 3, 2014 at 9:35 PM
Okay, so I'm answering my own question. Perhaps someone else will find it useful. Yes, commenting out the "SET DATEFORMAT YMD" line near the beginning of the stored procedure works. I have done some (very brief) testing without this line, and done a visual scan of the SQL and it appears that this line is not necessary. All of the date/string conversions appear to use either CONVERT with an explicit style code, or DATEPART. I cannot find or produce any problems without the SET DATEFORMAT YMD line.

Oct 27, 2014 at 2:29 PM
A better solution was to add "SET QUOTED_IDENTIFIER ON;" to the create script, immediately after RECONFIGURE; GO;
Marked as answer by DaveAtFigP on 10/27/2014 at 7:29 AM