July 7, 2004 at 7:33 am
When I try to write a select statement to retrieve data from a linked server using NOLOCK, I get the following error:
example: SELECT * FROM Server2.pubs.dbo.authors (NOLOCK)
Server: Msg 7377, Level 16, State 1, Line 1
Cannot specify an index or locking hint for a remote data
source.
Has anyone seen this before and have a work around. The only work around I have found is to change the query to set the transaction isolation level to Read Uncommitted.
July 7, 2004 at 11:05 am
You might try using the OPENQUERY syntax to do the select rather than the fully qualified syntax. I don't have the ability to test this right now as I'm at home.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
July 8, 2004 at 1:23 pm
It works, I did it many times
SELECT * FROM OpenQuery (Linked_Server2, 'select * from pubs.dbo.authors (NOLOCK)')
July 9, 2004 at 3:33 am
I got a response from a Microsoft SQL Tech. They did not like the idea of using the OPENROWSET or OPENQUERY functions. They said that was not really doing the same thing since I would be circumventing the hints on the remote server.
They informed me that I was partially correct in so far as this is not a bug it is a function by design (there is discussion if this function will be changed for Yukon or not). It was suggested as a workaround you could create a view on Server2 (i.e. something like CREATE VIEW authors_nolock AS SELECT * FROM pubs.dbo.authors WITH (NOLOCK)) and then query the view over the linked server without a hint; the isolation hint on the underlying table should be honored on Server2. Alternatively, I think you could also do something like CREATE VIEW authors_rmtview AS SELECT * FROM Server2.pubs.dbo.authors, …. Then do a SELECT * FROM authors_rmtview (NOLOCK) and you should get the same results.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply