Mar 18, 2014

Cannot execute query. Object '' is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.


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

Note: Assume Student is any table in same databse.

We will get error message like:

Cannot execute query. Object '' is specified in the TABLE HINT clause, but is not used in the query or does not match the alias specified in the query. Table references in the TABLE HINT clause must match the WITH clause.

Cause: It is due to object name Student in the Table Hint. It is necessary to specify same table name or table alias as a object name in the table hint. In the above query, It use tblSource table in the FROM clause while Student table in the table hint.

Solution:

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

Or


SELECT * FROM tblSource ts WHERE ID = 2
OPTION (TABLE HINT (ts, FORCESEEK));

No comments:

Post a Comment