I have been asked to ensure that my select queries selecting data from a linked remote server do not lock any tables/rows on the the remote server. I have been asked to accomplish this by adding the NOLOCK hint to each query.

Upon doing this, errors are returned in each query stating that the locking hint cannot be used with the remote server. The specific error is:

"Server: Msg 7377, Level 16, State 1, Line 8
Cannot specify an index or locking hint for a remote data source."


Is it possible to use the NOLOCK hint against the remote server or even possible to put a lock on a table on a remote server?

Am I going to have to consider re-writing my queries to use the OPENQUERY function?

Thanks!

I have the same question. Anyone have an answer for this?

I get around this by executing stored procedures on the linked remote server. Selecting the data locally on one server, then doing what I want with the data. Unfortunately I don't think MS will ever understand the need for row level locking. That or they don't know how to alter the SQL engine they origionally purchased from Sybase to do row level locking. Unfortunately these types of issues don't appear to be going away. Has anyone tried this on SQL 2005 yet???