Nolock Hint for remote query

• Jim Sue

  SSC Enthusiast

  Points: 108

  More actions

  April 28, 2005 at 10:41 am

  #87398

  Hi,

  I have a link server. A procedure is scheduled on local server to run every few minuts to bring data from a remote server to local server.

  We need to use (NOLOCK) hint on a select query, like

  Select * From RemoteServer.DBname.dbo.Customer (Nolock)

  Where status = 'N'

  And customer_id = 1234

  But (Nolock) is not allowed from SqlServer.

  We want to create a view on RemoteServer with (Nolock):

  Create View v_Customer

  As

  Select * From Customer (Nolock)

  Where status = 'N'

  Then, on the local server, use

  Select * From RemoteServer.DBname.dbo.v_Customer

  Where customer_id = 1234

  ( 1234 here is actually a parameter to pass in )

  I am wondering if the (Nolock) hint is really taking effect ?

  Thank You !

• Nicholas Cain

  SSC-Dedicated

  Points: 33009

  More actions

  April 28, 2005 at 1:17 pm

  #555674

  I believe that lock hints are ignored on link server queries, as such your nolock is not having any effect.

  Creating the view the way that you are however should force the hint to be observed.

  ---

  
  Shamless self promotion - read my blog http://sirsql.net