I have seen developers confused many times when they receive the following error message about Incorrect syntax near.
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.
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.
- Copy Database – SQL in Sixty Seconds #169
- 9 SQL SERVER Performance Tuning Tips – SQL in Sixty Seconds #168
- Excel – Sum vs SubTotal – SQL in Sixty Seconds #167
- 3 Ways to Configure MAXDOP – SQL in Sixty Seconds #166
- Get Memory Details – SQL in Sixty Seconds #165
Reference: Pinal Dave (https://blog.sqlauthority.com)
adding ; before merge(i.e like ;merge )
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.