I have a query that uses the FORCESEEK hint and runs fine on the server where the database I'm querying lives. If I try to run the same exact query remotely from a different server (targeting the original server), I get the following error:
Msg 7436, Level 16, State 1, Line 4 The query processor could not produce a query plan because FORCESEEK or FORCESCAN hints on table or view 'TableBeingQueried' cannot be used with remote data sources. Remove the hints and resubmit the query.
Example T-SQL:
SELECT DISTINCT Table3.Field5
FROM Server1.Database1.Table1 AS T1
INNER JOIN Server1.Database1.Table2 AS T2 WITH (FORCESEEK) -- Index exists for T2.Field2
ON T1.Field1 = T2.Field2
INNER JOIN Server1.Database1.Table3 AS T3 WITH (FORCESEEK) -- Index exists for T3.Field3
ON T2.Field4 = T3.Field3