If you performed an in place upgrade of your SQL Server, your master database may be at a lower compatibility level than all of the other system databases. This was done by conscious decision from Microsoft in an attempt to make for a smooth upgrade and ensure that existing applications can still function post upgrade. This has the negative side effect of not being able to use operators, keywords and other functionality when the database, in whose context you are executing these queries, is not at the right compatibility level.
For instance, if you want to examine currently executing queries using SQL 2005 with your master database in 80 compatibility …
SELECT s2.dbid,s2.objectid,SUBSTRING (s2.text, s1.statement_start_offset/2 + 1,
CASE WHEN s1.statement_end_offset = -1 THEN 8192
ELSE s1.statement_end_offset/2 – s1.statement_start_offset/2 END + 1) as QueryText
FROM sys.dm_exec_requests s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
… you will receive the following error…
Msg 321, Level 15, State 1, Line 5
“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function, ensure that your database compatibility mode is set to 90.
As a workaround, you can execute the same query against tempdb, model or msdb and it will work with no errors. It is permissible to change the compatibility level of the master database should you find it in a low compatibility level.
EXEC sp_dbcmptlevel ‘master’ , 90
EXEC sp_dbcmptlevel ‘master’ , 100
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.