I have an old database that I was having problems running a bit of code against. I was getting Incorrect syntax near '('. onthe following line of SQL:


FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')


It worked on other databases on the same server. I was perplexed and after checking a dozen other settings I find my problem.

select compatibility_level from sys.databases where name=db_name()
--gets the compatability_level for a database

I see that the database returned the value 80. This told me that this database was in in SQL 2000 compatibility mode. In compatibility 80 you can’t use a function as a parameter for another function.

I changed the database compatibility mode to 90 and everything worked great. Here is how I did it!

sp_dbcmptlevel
Sets certain database behaviors to be compatible with the specified version of SQL Server.

Example:
----SQL Server 2005 database compatible level to SQL Server 2000
EXEC sp_dbcmptlevel AdventureWorks, 80;
GO
----SQL Server 2000 database compatible level to SQL Server 2005
EXEC sp_dbcmptlevel AdventureWorks, 90;
GO

Version of SQL Server database can be one of the following:

• 60 = SQL Server 6.0
• 65 = SQL Server 6.5
• 70 = SQL Server 7.0
• 80 = SQL Server 2000
• 90 = SQL Server 2005

The sp_dbcmptlevel stored procedure affects behaviors only for the specified database, not for the entire server. sp_dbcmptlevel provides only partial backward compatibility with earlier versions of SQL Server. A database containing an indexed view cannot be changed to a compatibility level lower than 80.

The best practice to change the compatibility level of database is in following three steps.

• Set the database to single user access mode by using
  ALTER DATABASE SET SINGLE_USER
• Change the compatibility level of the database.
• Put the database in multiuser access mode by using
  ALTER DATABASE SET MULTI_USER