Compatiablity Error while using Merge Statement in the database in SQL Server

Once we have restored a database from SQL Server 2005 to SQL Server 2008 server, then we have executed a statement by using “Merge” as below:

MERGE Test_Merge as TM
USING
(
     SELECT
          *
     FROM Test
) as T
ON TM.EmpID = T.EmpID
WHEN MATCHED THEN
UPDATE SET
             TM.ENAME = T.ENAME,
             TM.DEPTNO = T.DEPTNO,
             TM.SAL = T.SAL,
             TM.DOB = T.DOB
WHEN NOT MATCHED THEN
INSERT
(
          ENAME
        , DEPTNO
        , SAL
        , DOB
)
VALUES
(
         T.ENAME
       , T.DEPTNO
       , T.SAL
       , T.DOB
)

when we use the above statement in the restored database, then we will be getting the below error:

<strong>Msg 325, Level 15, State 1, Line 67</strong>

Incorrect syntax near ‘MERGE’. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the SET COMPATIBILITY_LEVEL option of ALTER DATABASE.

<strong>Msg 156, Level 15, State 1, Line 70</strong>

<strong>Incorrect syntax near the keyword 'as'.

This error was due to the compatiablity level. As the compatiability level for the restored database in 90, first we need to update the compatiability level to 100, this can be done in the following way:

ALTER DATABASE [RestoredDatabase] SET COMPATIBILITY_LEVEL = 100
OR
SP_DBCMPTLEVEL 'RestoredDatabase',100
Leave a comment

3 Comments

  1. Kaushal

     /  January 14, 2015

    Hi Suneetha,

    I’m not getting any error while executing the script.
    but even after executing the script the DB_Level is not getting updated.
    I found out the reason, in the script “DB_NAME” is not coming proper. This script I’m executing through C# code(as per requirement).

    So my current work around is, set DB_NAME in C# itself and then execute the script.

    Thanks,
    Kaushal

  2. Hi Kushal,

    What is the error you are getting.

  3. Kaushal

     /  December 9, 2014

    Hi Suneetha,

    I’m doing same thing but in Post Deployment script of my SQL SErver Database Project.
    But it’s not working could you please help me with this.
    Script I’m executing:
    DECLARE @DataBaseName nvarchar(128)
    SET @DataBaseName = DB_NAME()
    EXEC sp_dbcmptlevel @DataBaseName, 100

    Thanks in advance.
    Regards,
    Kaushal

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: