A colleague of mine exposed, a few days ago, a situation where his customer had some scripts they ran successfully against a SQL Server 2008 R2 instance they had in production. No errors there… happy and easy life.
One day they decided to run those scripts also in a test server for a different platform they had and problems began. The exact same scripts started to fail with error “Incorrect Syntax near ‘)‘.” After some troubleshooting, they narrowed down the problem to a call to the undocumented/unsupported database enumerator stored procedure sp_MSforeachdb they were doing like this:
exec sp_MSforeachdb ‘use [?]; select * from sys.dm_db_index_physical_stats(db_id(),null,null,null,null)’;
The actual command was more convoluted, but that simplified version already allowed the repro. He also noticed that when he changed the first parameter to the DMV, from db_id() to a static value, it just ran without reporting any syntactical error. Of course, it didn’t have the same semantic meaning as the original version because the latter would return the results for, always the same database, as many times as databases were enumerated by the sp_MSforeachdb procedure.
He also noticed that, the following call to the same procedure worked and returned the database names of all databases:
exec sp_MSforeachdb ‘use [?]; select db_name(db_id())’;
So, all this conditions mixed arouse many different suspicions from the community where the case was exposed, ranging from lack of confidence on the reliability of sp_MSforeachdb, to wrong assumptions that local storage declared inside that procedure or in any of its calles could have been overflown, to almost as crazy ideas as suggesting that probably the relative humidity around the server was way too low.
After giving it a thought on what environmental conditions could affect the way in which the parser would process that input, database compatibility level came to my mind. And a quick test confirmed my suspicion.
It happened that the real cause for the error is that there was, at least, one database whose compatibility level was set to 80. And every time you try to run the following query in the context of any database with compatibility level 80, it will fail with that syntactic error:
select * from sys.dm_db_index_physical_stats(db_id(), null, null, null, null)
The reason? Well, in that compatibility level, the parser accepts passing to TVFs (internally known to the parser as table objects): 1) no parameters, or 2) a list of literal parameters. But it doesn’t accept any non-literals as params. Since DB_ID() is not a literal, that’s why it failed pointing right at the parenthesis after it had parsed that token.
If anyone would have suggested to try this alternative, it would have worked:
exec sp_MSforeachDB ‘use master; select * from sys.dm_db_index_physical_stats(db_id(”?”), null, null, null, null)’
Why? Because master would always be in a compatibility level matching that of the version installed and so, in its context, accepting non-literals as parameters to TVFs is supported.
Since that behavioral change is not explained in the ALTER DATABASE Compatibility Level topic in the documentation, I have filed a documentation work item so that it is explicitly documented in a future documentation refresh.
Meantime, get to know about that through this post.
In any case, I want to emphasize that you shouldn’t use sp_MSforeachdb since it is undocumented/unsupported and therefore it is not guaranteed that it will not be removed or its behavior completely modified without any previous warning in any future release.
Still, the point I was trying to make here is that, even if you create your own database enumeration mechanism, it could also fail with the same syntactical error and be bitten by this not yet documented behavioral change that depends on the compatibility level.