question

nidheesh.r.pillai avatar image
nidheesh.r.pillai asked

"Incorrect syntax near (" appearing for a valid query

I am using the below code to check the fragmentation percentage of one of my database to which my query editor points to SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, ips.index_type_desc AS IndexType, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips INNER JOIN sys.indexes ind ON ind.object_id = ips.object_id AND ind.index_id = ips.index_id WHERE ips.avg_fragmentation_in_percent > 20 I get the error as "Incorrect syntax near (" when I compile/execute the query. The problem seems to be when I mention DB_ID(). If I mention NULL or the database ID explicitly. there is no problem. -- This code works fine SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, ips.index_type_desc AS IndexType, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(16, NULL, NULL, NULL, 'LIMITED') ips INNER JOIN sys.indexes ind ON ind.object_id = ips.object_id AND ind.index_id = ips.index_id WHERE ips.avg_fragmentation_in_percent > 20 -- Similarly this too works fine SELECT OBJECT_NAME(ind.OBJECT_ID) AS TableName, ind.name AS IndexName, ips.index_type_desc AS IndexType, ips.avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(NULL, NULL, NULL, NULL, 'LIMITED') ips INNER JOIN sys.indexes ind ON ind.object_id = ips.object_id AND ind.index_id = ips.index_id WHERE ips.avg_fragmentation_in_percent > 20 I know the query is valid, and it does not show problems when I run the same query on a separate database instance on the same server (and on system databases as well). I want to know why the behavior is such when I mention DB_ID() in the query?
fragmentation
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

Kev Riley avatar image
Kev Riley answered
Check the compatibility level? From BOL > The built-in function DB_ID can be specified. When using DB_ID without specifying a database name, the compatibility level of the current database must be 90 or greater.
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Ow! Awesome! The compatibility level is 80. Should I change it?
2 Likes 2 ·
Should you change it? Will it break your application(s)? What version of SQL are you running? This will determine which values you can change it to, and therefore what difference it will make. Read the warnings and differences in BOL.
2 Likes 2 ·