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.
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.
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!
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!
Posts
