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:
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near ')'.
So to get around the initial 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:
Msg 102, Level 15, State 1, Line 3 Incorrect syntax near 'OBJECT_ID'.
So 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?
Of course, this is a compatibility level issue. The database 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 of these is the right answer, at least IMHO.
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…