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.
Personally, I find the compatibility level a bit inconsistent (see descriptions for sp_dbcmptlevel and ALTER DATABASE).
T-SQL 2000 simply did not have an OUTER APPLY, nor a fn_listextendedproperty() (without preceding ::), nor the sys schema.
Its description
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
devioblog 