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);

Filed under: Microsoft SQL Server, Problem Resolution, SQLServerPedia Syndication, T-SQL Tagged: code language, CROSS APPLY, language sql, microsoft sql server, problem resolution, sql statements, T-SQL