February 13, 2007 at 1:08 pm
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
February 13, 2007 at 2:06 pm
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.
February 14, 2007 at 1:34 am
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
February 14, 2007 at 2:02 pm
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.
August 26, 2008 at 11:22 am
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply