SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

“sql_handle” is not a recognized table hints option.

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

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...