T-SQL Syntax Error due to Compatibility Level

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.