Linked Servers and Nolock

Question

Linked Servers and Nolock

Susan-322874

SSCrazy

Points: 2300
More actions
February 13, 2007 at 1:08 pm

#172033

When I try and run a query against a link server with a nolock, I get an error message saying
"Cannot specify an index or locking hint for a remote data source."
I just need to collect some data from a heavily used leads queue table and can't lock the table
while running the query. Has anyone else had this issue? If so, any work arounds?

Thanks in advance
Susan

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

You could try creating a view on the remote server/db and specifying nolock in the view I believe. Then just select from the view.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You can use openquery and put the nolock inside

SELECT * FROM OpenQuery(server,'Select * from sometable with (nolock)')

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Omri Bahat-228920 Mr or Mrs. 500 Points: 540 More actions · Answer 3

Use SET TRANSACTION ISOLATION LEVEL instead of (NOLOCK) - this has the exact same effect as (NOLOCK) over linked servers.

Example:

If you wish to do the following (which is not permitted):

SELECT * FROM MyLinkedServer.MyDB.dbo.Table1 WITH (NOLOCK)

Instead, do:

SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED

SELECT * FROM MyLinkedServer.MyDB.dbo.Table1

-- set back to original isolation level

SET TRANSACTION ISOLATION LEVEL READCOMMITTED

The two are synonymous.

Editor's Note: : Dr. Omri Bahat works for SQLFarms, a software vendor of SQL Server tools.

rubes SSCrazy Points: 2058 More actions · Answer 4

Something must have changed within the last year because, to my surprise and delight, the following now works:

SELECT * FROM MyLinkedServer.MyDB.dbo.Table1 WITH (NOLOCK)

This works from 2005 to 2000 and from 2005 to 2005. The 2005 build I verified this against was 3054.

Regards,
Rubes