Link

Hello. Have a cross-server query that we use a NOLOCK so
nobody complains about locks on their server. ANyway,
this works ok on SQL 7, but running the exact same code on
SQL 2000 I get the error below as though you can't do a
NOLOCK now when using a Linked Server under SQL 2000'
Any ideas on that or a better way to enure that we do as
few locks as possible on that other server we're pulling
data from? Thanks, Bruce
select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
Server: Msg 7377, Level 16, State 1, Procedure PROC_NAME
Cannot specify an index or locking hint for a remote data
source.HI,
Create stored procedure on Linked server:
CREATE PROCEDURE Test
AS
select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
----
-
Change the select statement to following:
EXECUTE [Linked Server].[Database].[Owner].[Test]
JBandi|||Bruce,
If you create the sp on the foreign system, you would NOT have to use the
4part name in the sp.. Only use the 2-part name in the sp, and use the
4-part name to call it from your primary server.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:102a501c414e2$f34ab360$a001280a@.phx
.gbl...
> Hello. Have a cross-server query that we use a NOLOCK so
> nobody complains about locks on their server. ANyway,
> this works ok on SQL 7, but running the exact same code on
> SQL 2000 I get the error below as though you can't do a
> NOLOCK now when using a Linked Server under SQL 2000'
> Any ideas on that or a better way to enure that we do as
> few locks as possible on that other server we're pulling
> data from? Thanks, Bruce
> select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
> Server: Msg 7377, Level 16, State 1, Procedure PROC_NAME
> Cannot specify an index or locking hint for a remote data
> source.|||Thanks. I was actually looking for a way to NOT put any
SP objects in the database we're accessing (political
thing!)... Also, the code wa write with direct join in
these cross-server links. THe NOLOCK option works in SQL
7, but it looks like with SQL 2000 you cannot say NOLOCK
on a remote linked server query?
Is there any other way to directly access data in the
other database without SP's ON that other database, so you
cause as few locks as possible on that database?
Thanks, Bruce

>--Original Message--
>Bruce,
>If you create the sp on the foreign system, you would NOT
have to use the
>4part name in the sp.. Only use the 2-part name in the
sp, and use the
>4-part name to call it from your primary server.
>--
>Wayne Snyder, MCDBA, SQL Server MVP
>Computer Education Services Corporation (CESC),
Charlotte, NC
>www.computeredservices.com
>(Please respond only to the newsgroups.)
>I support the Professional Association of SQL Server
(PASS) and it's
>community of SQL Server professionals.
>www.sqlpass.org
>
>"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
> news:102a501c414e2$f34ab360$a001280a@.phx
.gbl...
so
on
data
>
>.
>|||Thanks. Yes, we could do that but there are issue with
anyone putting any objects in "their" database (yea, even
though we can ACCESS their database!?!)... Looking for a
NOLOCK type of situation where we can retrieve from their
database... THanks, Bruce

>--Original Message--
>HI,
>Create stored procedure on Linked server:
>CREATE PROCEDURE Test
>AS
>select col1, col2 FROM SERVER.DB.dbo.TABLE WITH (NOLOCK)
>----
--
>
>Change the select statement to following:
>EXECUTE [Linked Server].[Database].[Owner].[Test]
>JBandi
>.
>|||You can try a pass through query, maybe that would do it..
ie
select * from openquery(linkedservername, select col1, col2 FROM
DB.dbo.TABLE WITH (NOLOCK))
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Bruce de Freitas" <bruce@.defreitas.com> wrote in message
news:14f7401c4158b$d6ebc6b0$a501280a@.phx
.gbl...
> Thanks. I was actually looking for a way to NOT put any
> SP objects in the database we're accessing (political
> thing!)... Also, the code wa write with direct join in
> these cross-server links. THe NOLOCK option works in SQL
> 7, but it looks like with SQL 2000 you cannot say NOLOCK
> on a remote linked server query?
> Is there any other way to directly access data in the
> other database without SP's ON that other database, so you
> cause as few locks as possible on that database?
> Thanks, Bruce
>
> have to use the
> sp, and use the
> Charlotte, NC
> (PASS) and it's
> so
> on
> data