Wrox Programmer Forums
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old December 7th, 2004, 03:58 PM
Registered User
Join Date: Jul 2003
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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?

Old May 10th, 2006, 03:49 PM
Registered User
Join Date: May 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts

I have the same question. Anyone have an answer for this?
Old May 10th, 2006, 04:30 PM
Friend of Wrox
Join Date: Aug 2004
Posts: 385
Thanks: 0
Thanked 0 Times in 0 Posts

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???

Similar Threads
Thread Thread Starter Forum Replies Last Post
Locking in Access DB linked to SQL Server backend gibbers Access 3 April 24th, 2007 03:20 PM
Access to SQL Server locking problem echovue Access VBA 2 December 22nd, 2006 08:10 AM
VB Express and SQL server on remote server questio HoosierDaddy61 Visual Basic 2005 Basics 0 February 22nd, 2006 11:27 AM
SQL Server Database Locking with Access thatoneguy SQL Server 2000 3 September 7th, 2005 08:05 PM
connecting web server and remote db server via asp moreyt Classic ASP Databases 0 May 31st, 2005 12:13 AM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.