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
Showing posts with label pack. Show all posts
Showing posts with label pack. Show all posts
Friday, February 24, 2012
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:
thread/6b285527d118f3b8/29d97cfedeb0cb06?q=nolock+linked+server&rnum=5&hl=en#29d97
cfedeb0cb06" target="_blank">http://groups.google.com.au/group/m.../>
cfedeb0cb06
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/m...server/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
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:
thread/6b285527d118f3b8/29d97cfedeb0cb06?q=nolock+linked+server&rnum=5&hl=en#29d97
cfedeb0cb06" target="_blank">http://groups.google.com.au/group/m.../>
cfedeb0cb06
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/m...server/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
Linked Server "NOLOCK" problem on MS 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 ?What DBMS is the remote table on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <cpjohn@.netvigator.com> wrote in message news:d7i07b$ir39@.imsp212.netvigator.com...[v
bcol=seagreen]
> Dear all,
> My current configuration : Windows 2000 Server + MS SQL Server 2000 Standa
rd
> Edition + Service Pack 3
> I got an error when I used "NOLOCK" in selecting some records through Link
ed
> 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 ?
>
>[/vbcol]
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 ?What DBMS is the remote table on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <cpjohn@.netvigator.com> wrote in message news:d7i07b$ir39@.imsp212.netvigator.com...[v
bcol=seagreen]
> Dear all,
> My current configuration : Windows 2000 Server + MS SQL Server 2000 Standa
rd
> Edition + Service Pack 3
> I got an error when I used "NOLOCK" in selecting some records through Link
ed
> 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 ?
>
>[/vbcol]
Linked Server "NOLOCK" problem on MS 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 ?
What DBMS is the remote table on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <cpjohn@.netvigator.com> wrote in message news:d7i07b$ir39@.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 ?
>
>
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 ?
What DBMS is the remote table on?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"John" <cpjohn@.netvigator.com> wrote in message news:d7i07b$ir39@.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 ?
>
>
Subscribe to:
Posts (Atom)