Locking on a Remote Server
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?