SQL 2012 Bug - Incorrect Syntax Need BEGIN Statements

SQL 2012 Bug - Incorrect Syntax Need BEGIN Statements

We recently moved Database servers from 2008 to 2012.

Everything seemed to have gone fine until we found that one of our logger database tables had grown to a massive size holding millions of rows and containing many more days records than it should have.

As this was over the Christmas period no-one really noticed until we got back from holiday.However a look in the log files from the MS Agent job showed that the part of the Stored Procedure to delete old records was failing and then the proc was bombing out, which meant none of the older historical records were being deleted.

The error message in the log file was "Incorrect Syntax near begi" and then just a load of failed statements.

There are some Microsoft knowledge base articles about this issue that say the problem is due to:

• The statement contains an IF condition.
• The IF condition does not contain BEGIN and END statements.
• The IF condition is followed by a BEGIN TRY block.
• The IF block is recompiled when you run the query. 

You can read more about the problem on Microsofts own sites:

https://connect.microsoft.com/SQLServer/feedback/details/752276/incorrect-syntax-near-begi-in-sql-2012

and

http://support.microsoft.com/kb/2829372

They actually provide this example code which is supposed to replicate the error in MS SQL 2012 however when I ran it in a query window it worked fine for me.

DECLARE @i INT

IF object_id('tempdb..#temptable') IS NOT NULL
DROP TABLE #temptable

CREATE TABLE #temptable (id INT)
INSERT INTO #temptable VALUES (1),(2),(3);

IF year(getdate())=2012 SELECT @i=(SELECT COUNT(*) AS nr FROM #temptable);
BEGIN TRY
SELECT 'message'
END TRY

BEGIN CATCH
SELECT ERROR_MESSAGE()
END CATCH

Therefore I then thought that maybe the problem might not be the actual language and queries but something to do with Stored Procedures CONTAINING such language.

The stored procedures that were bombing out were full of statements that had IF statements near BEGIN TRY without an END before it. For example we put in DEBUG statements for logging and being able to debug the code after the midnight hours so we would have code like this all over the place.

DECLARE @i INT

IF @DEBUG = 1
  PRINT 'About to run code to delete old historical records'

BEGIN TRY
  ...code
END TRY
BEGIN CATCH
 .. code
END CATCH

As our DBA didn't want to install a "dodgy" hotfix as he called it, even though SP1 had been installed (which didn't fix the issue) to test this theory out we made sure any of our IF statements were wrapped in BEGIN ..code... END statements like this.

DECLARE @i INT

IF @DEBUG = 1
  BEGIN
       PRINT 'About to run code to delete old historical records'
  END

BEGIN TRY
  ...code
END TRY
BEGIN CATCH
 .. code
END CATCH

So we put the new code into our proc and let it run that night from MS Agent.

Guess what?

When we ran the MS Agent job that night that fired off these stored procedures the whole thing worked!

I don't know if it is necessarily a stored procedure problem or not but this simple fix for our own code seemed to solve the problem without having to apply the hotfix so it seemed to solve the problem for us.

How Microsoft have gotten so far into their roll out of SQL 2012 with such a problem in their code I have no idea but it seems to be such an annoying bug that a lot of people are complaining about it.

Hopefully this solution may fix it all for you as well!