Unexpected errors from table-valued functions
They want to use a dynamic management function with parameters, for example:
SELECT * FROM sys.dm_db_index_physical_stats ( DB_ID(), OBJECT_ID('sys.objects'), NULL, NULL, NULL );
On some databases, they get the following error:
Incorrect syntax near ')'.
To get around the syntax error, they do something like this, hard-coding the database_id:
SELECT * FROM sys.dm_db_index_physical_stats ( 1, OBJECT_ID('sys.objects'), NULL, NULL, NULL );
…only to be met by a slightly different error:
Incorrect syntax near 'OBJECT_ID'.
For some reason, neither DB_ID() nor OBJECT_ID() could be used within this function call. Can you imagine what might be going wrong here?
The database where this error happens is set to 80 (SQL Server 2000) compatibility level. A lot of folks who discover this jump to the conclusion that either:
- they should just change the compatibility level to something more modern; or,
- that it's not possible to call these dynamic management functions when running in 80 compatibility.
Neither is true. You shouldn't just change the compatibility level from 2000, because you may not have any idea *why* the database is still in that mode; there may be serious application issues preventing the change (from known problems to unknown unknowns).
And it *is* still possible to call these functions without hard-coding the IDs – as Paul's blog post pointed out – it's not the table-valued functions that are the problem, it's the ability to pass in function calls as arguments. So you can change the code to the following in order to overcome the issue:
DECLARE @database_id int = DB_ID(), @object_id int = OBJECT_ID('sys.objects'); SELECT * FROM sys.dm_db_index_physical_stats ( @database_id, @object_id, NULL, NULL, NULL );
This will work in any SQL Server 2005+ database, regardless of the compatibility level.
Now, that doesn't mean you should keep holding onto 2000 compatibility mode – it won't work in SQL Server 2012, so perhaps you finally have the motivation to fix the issues that will ultimately block your migration. I talk about several other differences you might have to deal with in this dba.stackexchange answer.