Mar 19, 2014

"" 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.


1. Cause: Due to invalid table hints

Suppose we have created a table and inserted some data using following query:

CREATE TABLE tblSource(
    ID INT IDENTITY PRIMARY KEY,
    vcData VARCHAR(500)
)

INSERT INTO tblSource VALUES('PC'),('Laptop'),('Mobile')

Now if we will execute this sql query:

SELECT * FROM tblSource WHERE ID = 2
OPTION (TABLE HINT (Student, FORCESEEK1));

Note: Assume Student is any table in same database.

We will get error message like:

"" 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.
  
Cause: Table hints we have passed is invalid. Valid table hints are:

Table Hints
INDEX
FASTFIRSTROW
FORCESEEK
FORCESCAN
HOLDLOCK
NOLOCK
NOWAIT
PAGLOCK
READCOMMITTED
READCOMMITTEDLOCK
READPAST
READUNCOMMITTED
REPEATABLEREAD
ROWLOCK
SERIALIZABLE
TABLOCK
TABLOCKX
UPDLOCK
XLOCK

Solution:

Use valid table hints. For example:

SELECT * FROM tblSource WHERE ID = 2
OPTION (TABLE HINT (Student, FORCESEEK1));

2. Cause: Due to using CHANGETABLE

Suppose we are executing following sql query:

CREATE TABLE tblPrice(
    ItemId BIGINT IDENTITY PRIMARY KEY,
    Price MONEY,
)

INSERT tblPrice VALUES(50),(30),(40)

ALTER DATABASE TableStruct
SET CHANGE_TRACKING = ON

ALTER TABLE tblPrice
ENABLE CHANGE_TRACKING
WITH(TRACK_COLUMNS_UPDATED = ON)

DECLARE @LastSynchronizeVersion AS BIGINT
DECLARE @ObjectID AS INT

SET @ObjectID = OBJECT_ID('tblPrice')
SET @LastSynchronizeVersion = CHANGE_TRACKING_MIN_VALID_VERSION(@ObjectID)

SELECT
    p.Price,
    CT.ItemId,
    CT.SYS_CHANGE_VERSION

FROM CHANGETABLE(CHANGES tblPrice,@LastSynchronizeVersion ) AS CT
INNER JOIN tblPrice p
ON  p.ItemId = CT.ItemId

We may get error message like:

"" 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.

Cause: we cannot use CHAGETABLE function in the database which compatibility level is 80

Solution:

ALTER DATABASE TableStruct
SET COMPATIBILITY_LEVEL = 100

No comments:

Post a Comment