Compatibility error when using MERGE. Incorrect syntax near 'MERGE'.

04 July,2014 by Jack Vamvas

When restoring a  database from SQL Server 2005 to SQL Server 2012 – and attempting to execute  CREATE PROCEDURE statement  with   a MERGE  keyword   , the following errors  appeared :

Msg 102, Level 15, State 1, Procedure MyStoredProcedure, Line 159
Incorrect syntax near 'MERGE'.
Msg 156, Level 15, State 1, Procedure MyStoredProcedure, Line 231
Incorrect syntax near the keyword 'as'.
Msg 102, Level 15, State 1, Procedure MyStoredProcedure, Line 389
Incorrect syntax near 'END'.

 

The errors were caused by the Database Compatibility level set at 90. Changing the database compatibility to 100 fixed the problem.

Why is this happening?

The first clue is in SQL Server BOL  “Compatibility-level setting of 90 or lower: MERGE is not enforced as a reserved keyword. Compatibility-level setting of 100: MERGE is a fully reserved keyword. The MERGE statement is supported under both 100 and 90 compatibility levels”

This means  you can use MERGE on SQL Server 2008 and higher versions, with compatibility mode at 90. Using compatibility level 90 impacts the keyword enforcement , keyword enforcement impacts parsing , but not the execution phase. 

Returning back to the original problem – when I was running the CREATE PROCEDURE statement with compatibility = 90 , the parsing phase of CREATE PROCEDURE didn’t interpret the MERGE keyword as reserved, therefore throwing an error.

To check the database compatibility level , use this syntax:

 

 
SELECT compatibility_level
FROM sys.databases WHERE name = 'MyDB';
To alter the database compatibility level:
ALTER DATABASE [MyDB] SET COMPATIBILITY_LEVEL = 100


 

Related Posts

SQL server – 5 things SQL Server developers should know about SQL Server

SQL Server Performance Killers

Stored Procedures versus ad-hoc paramaterized queries.Which is faster?

ISO-11179 Naming Conventions and SQL DDL


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on Compatibility error when using MERGE. Incorrect syntax near 'MERGE'.


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer