My application dbscript queries the system catalog views to retrieve a database schema, and uses different SELECT statements for SQL Server 2000 and for 2005 and higher. To my surprise, it recently raised a syntax error when reading from a 2008 database:
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ‘sys’.
The statements typically look like this
SELECT sys.xml_schema_collections.xml_collection_id, sys.schemas.name as schema_name, sys.xml_schema_collections.name, ep.value as Description FROM sys.xml_schema_collections INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.xml_schema_collections.schema_id OUTER APPLY fn_listextendedproperty('MS_Description', 'SCHEMA', sys.schemas.name, 'XML SCHEMA COLLECTION', sys.xml_schema_collections.name, null, null) ep WHERE xml_collection_id > 1 ORDER BY sys.xml_schema_collections.name
Note the OUTER APPLY invocation of the table-valued function fn_listextendedproperty().
A quick search led me to this thread suggesting that the compatibility level of the database may not be the most current (100 for SQL Server 2008). I checked, and indeed compatibility level was 80 as a result of restoring a SQL Server 2000 database. Setting the compatibility level to 100 fixed the problem.
T-SQL 2000 simply did not have an OUTER APPLY, nor a fn_listextendedproperty() (without preceding ::), nor the sys schema.
Sets certain database behaviors to be compatible with the specified version of SQL Server
does not mention APPLYs or table-valued functions. And it’s not clear why exactly a parameter to a TVF is causing the syntax error. I guess that’s outside the scope of “certain behaviors”.
Lesson learned: when restoring a database from a different server version, check the compatibility levels 😉
Here’s the statement to do so:
select name, compatibility_level from sys.databases