“sql_handle” is not a recognized table hints option.

Leave a comment

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

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 )

Google+ photo

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

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 1,646 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: