Linked Server "NOLOCK" problem on SQL 2000

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 ?
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/mi...d97cfedeb0cb06
Regards--Mike Epprecht, Microsoft SQL Server
MVPZurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"John" <cpjohn@.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 ?
>
>
>
|||Thanks.
"Mike Epprecht (SQL MVP)" <mike@.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/mi...erver/browse_t
hread/thread/6b285527d118f3b8/29d97cfedeb0cb06?q=nolock+linked+server&rnum=5
&hl=en#29d97cfedeb0cb06
> Regards--Mike Epprecht, Microsoft SQL Server
> MVPZurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "John" <cpjohn@.netvigator.com> wrote in message
> news:d7ce0j$b4f25@.imsp212.netvigator.com...
>
|||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