Linked Server "NOLOCK" problem on SQL 2000

28 May 2005 1:07 PM

John

Dear all,

My current configuration : Windows 2000 Server + MS SQL Server 2000 Standard
Edition + Service Pack 3

I got an error when I used "NOLOCK" in selecting some records through Linked
Server :

SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran (NOLOCK)

Error :

Server : Msg 7377, Level 16, State 1, Line 1
Cannot specify an index or locking hint for a remote data source

But this error would not occurred if I re-wrote the statement :

SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran

Why "NOLOCK" doesn't work in SQL 2000 Linked Server ?

2 Jun 2005 8:50 PM

Mike Epprecht (SQL MVP)

Hi

OPENQUERY can pass the NOLCOK hint It is one of those limitations that
probably has a valid technical and implementation reason, but the SQL Server
development team have not said why.

Look also at:

http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_thread/thread/6b285527d118f3b8/29d97cfedeb0cb06?q=nolock+linked+server&rnum=5&hl=en#29d97cfedeb0cb06
Regards--------------------------------Mike Epprecht, Microsoft SQL Server
MVPZurich, Switzerland
IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quoteHide quote

"John" <cpj***@netvigator.com> wrote in message
news:d7ce0j$b4f25@imsp212.netvigator.com...
> Dear all,
>
> My current configuration : Windows 2000 Server + MS SQL Server 2000
> Standard
> Edition + Service Pack 3
>
> I got an error when I used "NOLOCK" in selecting some records through
> Linked
> Server :
>
> SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran (NOLOCK)
>
> Error :
>
> Server : Msg 7377, Level 16, State 1, Line 1
> Cannot specify an index or locking hint for a remote data source
>
> But this error would not occurred if I re-wrote the statement :
>
> SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran
>
> Why "NOLOCK" doesn't work in SQL 2000 Linked Server ?
>
>
>
>
>
>

3 Jun 2005 3:58 AM

John

Thanks.

"Mike Epprecht (SQL MVP)" <m***@epprecht.net> wrote in message
news:u9OUzS7ZFHA.1384@TK2MSFTNGP09.phx.gbl...
> Hi
>
> OPENQUERY can pass the NOLCOK hint It is one of those limitations that
> probably has a valid technical and implementation reason, but the SQL
Server
> development team have not said why.
>
> Look also at:
>
>

http://groups.google.com.au/group/microsoft.public.sqlserver.server/browse_t
hread/thread/6b285527d118f3b8/29d97cfedeb0cb06?q=nolock+linked+server&rnum=5
&hl=en#29d97cfedeb0cb06
Show quoteHide quote

> Regards--------------------------------Mike Epprecht, Microsoft SQL Server
> MVPZurich, Switzerland
> IM: m***@epprecht.net
>
> MVP Program: http://www.microsoft.com/mvp
>
> Blog: http://www.msmvps.com/epprecht/
>
> "John" <cpj***@netvigator.com> wrote in message
> news:d7ce0j$b4f25@imsp212.netvigator.com...
> > Dear all,
> >
> > My current configuration : Windows 2000 Server + MS SQL Server 2000
> > Standard
> > Edition + Service Pack 3
> >
> > I got an error when I used "NOLOCK" in selecting some records through
> > Linked
> > Server :
> >
> > SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran (NOLOCK)
> >
> > Error :
> >
> > Server : Msg 7377, Level 16, State 1, Line 1
> > Cannot specify an index or locking hint for a remote data source
> >
> > But this error would not occurred if I re-wrote the statement :
> >
> > SELECT * FROM TKOA_LINK.sales_data.dbo.sales_tran
> >
> > Why "NOLOCK" doesn't work in SQL 2000 Linked Server ?
> >
> >
> >
> >
> >
> >
>
>

18 Jul 2005 8:58 AM

Paul Cahill

We get around this by exec'ing a remote stored proc.

exec @RetVal = thelinkedserver.dbname.myproc @param1 = @param1 .....
etc

The limitations are that you can't insert data from exec into a table
variable. normal tables and #tables are OK.
Also you can't exec another proc in the remote proc.

You should get better performance using this as well.

Paul