Unanswered: Cannot specify an index or locking hint for a remote data source - Msg 7377
Server: Msg 7377, Level 16, State 1, Line 17
Cannot specify an index or locking hint for a remote data source.
I get the above error when i execute the following code using lined servers in SQLServer.
Does microsoft have a fix for this? It seems in SQLServer sp3 also , its not fixed.
There is a workaround for this using OPENQUERY, but that also has issues like we cannot define variables in it. Also there are performance issues in OPENQUERY.
Does anyone have an idea of solving this?
SET XACT_ABORT ON
BEGIN DISTRIBUTED TRANSACTION
SELECT GD.GUI AS [T_GUI],
'PRI' AS [T_NAME_TYPE],
LEFT(BP.FIRSTNAME,30) AS [T_FIRST_NAME],
LEFT(BP.LASTNAME,30) AS [T_LAST_NAME],
LEFT(BP.MIDDLENAME,30) AS [T_MIDDLE_NAME],
LEFT(BP.PREFIX,4) AS [T_NAME_PREFIX],
LEFT(BP.SUFFIX,15) AS [T_NAME_SUFFIX],
LEFT(BP.SECONDLASTNAME,30) AS [T_SECOND_LAST_NAME],
WHEN BP.DELETED = 0 THEN 'N'
END AS [T_DELETE_FLAG]
FROM METECHMEYTEST02.HRMS.HRADMIN.STAFF S WITH (UPDLOCK)
INNER JOIN MACHINE1.HR.HRADMIN.GDETAILS GD WITH (UPDLOCK) ON S.UUID=GD.STAFF_UUID
LEFT OUTER JOIN MACHINE1.HR.HRADMIN.BASICPROFILE BP WITH (UPDLOCK) ON S.PROFILEID=BP.UUID