September 23, 2015 by Kenneth Fisher
I’ve you’ve run a query similar to the following:
SELECT * FROM sys.dm_exec_requests CROSS APPLY sys.dm_exec_sql_text(sql_handle);
And gotten an error similar to:
Msg 321, Level 15, State 1, Line 9
“sql_handle” is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
Then first of all, I’m sorry. You are not only working on a 2008 R2 (or earlier) instance but you are working with a compatibility level 80 (SQL 2000) database. SQL Server 2008 R2 is the last version that allows a compatibility level of 80 so after this point you won’t see this problem any more.
Specifically you get an error when using CROSS APPLY with a Table Valued Function (In my example above the system TVF sys.dm_exec_sql_text) then you get the error I mentioned if you are in the context of a compatibility level 80 database. Now this ONLY happens when you are in the context of of a compatibility level 80 database. If you switch your context to a different database, say master (USE master), which has a compatibility level of 90 or greater then the bug goes away. Even if you are using a custom TVF that you built in the compatibility level 80 database.
If you are convinced, you can stop here. If not here is a quick example using a 2008 R2 instance.
CREATE DATABASE Compat80; GO ALTER DATABASE Compat80 SET COMPATIBILITY_LEVEL = 80; GO USE Compat80; GO CREATE FUNCTION TVF (@Col1 int) RETURNS TABLE AS RETURN (SELECT @COl1 AS Col1); GO
This one returns an error:
USE Compat80; GO SELECT * FROM sys.databases CROSS APPLY Compat80.dbo.TVF(database_id);
This one does not:
USE master; GO SELECT * FROM sys.databases CROSS APPLY Compat80.dbo.TVF(database_id);