SQL SERVER – Fix : Error : Incorrect syntax near. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel

I have seen developers confused many times when they receive the following error message about Incorrect syntax near.

SQL SERVER - Fix : Error : Incorrect syntax near. You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel incorrectsyntaxnear-800x198

Msg 325, Level 15, State 1, Line 7
Incorrect syntax near . You may need to set the compatibility level of the current database to a higher value to enable this feature. See help for the stored procedure sp_dbcmptlevel.

The reason for this error is when the user is trying to attempt to run a query or procedure or logic which is not compatible with the previous version of the SQL Server. When SQL Server 2000 is upgraded to SQL Server 2005 or SQL Server 2008, the database object compatibility should be also upgraded to the next version. When database compatibility is set to the previous version and they are attempted with the procedure of the newer version they will throw the above error.

Fix/Workaround/Solution:

Change the database compatibility level using the following command.

For SQL Server 2005:

EXEC sp_dbcmptlevel 'DatabaseName', 90

For SQL Server 2008:

EXEC sp_dbcmptlevel 'DatabaseName', 100

I hope this will help you to fix the incorrect syntax near. You can reach out to me on Twitter.

Here are my few recent videos and I would like to know what is your feedback about them.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Transaction and Local Variables – Swap Variables – Update All At Once Concept
Next Post
SQLAuthority News – Running SQL Server 2008 in a Hyper-V Environment Best Practices and Performance Considerations

Related Posts

87 Comments. Leave new

  • adding ; before merge(i.e like ;merge )

    Reply
  • I am setting up a job in SQL Server Agent in SQL Server 2000, and successfully parsed this line: EXEC sp_dbcmptlevel ‘QMS Richmond’, 80; However, when I added this line: BACKUP LOG ‘QMS Richmond’ WITH TRUNCATE_ONLY;. with error message Incorrect syntax near ‘QMS Richmond’. How to correctly using sp_dbcmptlevel in this matter? How to solve my problem? Thank you.

    Reply

Leave a Reply

Menu