I've seen a few people stymied by this one recently, even though @PaulRandal wrote a mythbuster article about it last April. (If you don't want a spoiler alert, don't click through just yet.)
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.
Awesome response Aaron. I ran into this issue and this blog help me resolve this quirk.
Hi Aaron
Nice to see another reason to move away from SQL2000. I blogged one here: http://thelonedba.wordpress.com/2011/10/04/t-sql-tuesday-23-non-ansi-joins-discontinued-in-denali/
Cheers
Robert, yes that's true, but I wanted to point out that your technique works as is for functions that work across databases, but doesn't work quite the same way for local DMFs (e.g. sys.dm_sql_referenced_entities). Not that for that specific one you'd try to use OBJECT_NAME(), but you could, and it would fail in an 80 compatible database. The syntax works if you call it from another database using a three-part name, but you still need to resolve names/IDs in the target. On a related aside, I think instead of:
OBJECT_ID('sys.objects', 'TestFGRestores2')
You meant:
OBJECT_ID('TestFGRestores2.sys.objects')
Database name is not a valid parameter to OBJECT_ID, nor to OBJECT_NAME/OBJECT_SCHEMA_NAME (which accept database_id). Your example worked by chance because sys.objects has the same object_id in all databases, and the second parameter is ignored if unrecognized – but it would not work so well for a user object without the target database prefix.
Another option (and IIRC correctly, Paul Randal has blogged this option already) is to run it in master database (or any database in the higher compatibility mode) and pass in the database name to the functions.
For example, I changed the TestFGRestores2 database to compat 80 and then I could run this successfully:
Use master;
SELECT *
FROM sys.dm_db_index_physical_stats(
DB_ID('TestFGRestores2'),
OBJECT_ID('sys.objects', 'TestFGRestores2'),
NULL, NULL, NULL);