Error : Cannot specify an index or locking hint for a remote data source

We are migrating from sql 7 to sql 2000 and for the time
being leaving the database in sql 7 compatabiliy mode.
Some of the stored procedures reference the tables on a
linked server. The linked server is running sql 2000 and
the database is sql 2000.
When I run the following command
select * from linked_server.database.dbo.tablename (nolock)
I receive the following error
Cannot specify an index or locking hint for a remote data
source.
If I remove the () around nolock the statement runs.
The question(s) I have
1. Did I set something up wrong?
2. Without the () is the nolock still performing the same
function?
3. Assumining I did not error on the configuration, Why do
I receive this error now, and not before?
4. Any ideas to fix the problem without removing the ()?
Thanks in advance for any suggestions/ideas.
John WilliamsWithout the brackets around nolock, the SQL query engine is assigning the
word 'nolock' as an alias to your table in the select statement (i.e.
equivalent to 'as [nolock]').
R
"John Williams" <anonymous@.discussions.microsoft.com> wrote in message
news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
> We are migrating from sql 7 to sql 2000 and for the time
> being leaving the database in sql 7 compatabiliy mode.
> Some of the stored procedures reference the tables on a
> linked server. The linked server is running sql 2000 and
> the database is sql 2000.
> When I run the following command
> select * from linked_server.database.dbo.tablename (nolock)
> I receive the following error
> Cannot specify an index or locking hint for a remote data
> source.
> If I remove the () around nolock the statement runs.
> The question(s) I have
> 1. Did I set something up wrong?
> 2. Without the () is the nolock still performing the same
> function?
> 3. Assumining I did not error on the configuration, Why do
> I receive this error now, and not before?
> 4. Any ideas to fix the problem without removing the ()?
> Thanks in advance for any suggestions/ideas.
> John Williams|||Can you not create a stored procedure on the remote data source that
specifies the hint?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"John Williams" <anonymous@.discussions.microsoft.com> wrote in message
news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
> We are migrating from sql 7 to sql 2000 and for the time
> being leaving the database in sql 7 compatabiliy mode.
> Some of the stored procedures reference the tables on a
> linked server. The linked server is running sql 2000 and
> the database is sql 2000.
> When I run the following command
> select * from linked_server.database.dbo.tablename (nolock)
> I receive the following error
> Cannot specify an index or locking hint for a remote data
> source.
> If I remove the () around nolock the statement runs.
> The question(s) I have
> 1. Did I set something up wrong?
> 2. Without the () is the nolock still performing the same
> function?
> 3. Assumining I did not error on the configuration, Why do
> I receive this error now, and not before?
> 4. Any ideas to fix the problem without removing the ()?
> Thanks in advance for any suggestions/ideas.
> John Williams|||I am not sure if I understand what you are asking? Are you
suggesting that the stored procedure we run on the server
that has the syntax.
Select & from linkerserver.database.dbo.table1 and instead
run a stored procedure on the linked server that
accomplishes the same thing? I can look into this as an
option.
Thanks...
>--Original Message--
>Can you not create a stored procedure on the remote data
source that
>specifies the hint?
>--
>http://www.aspfaq.com/
>(Reverse address to reply.)
>
>
>"John Williams" <anonymous@.discussions.microsoft.com>
wrote in message
>news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
>> We are migrating from sql 7 to sql 2000 and for the time
>> being leaving the database in sql 7 compatabiliy mode.
>> Some of the stored procedures reference the tables on a
>> linked server. The linked server is running sql 2000 and
>> the database is sql 2000.
>> When I run the following command
>> select * from linked_server.database.dbo.tablename
(nolock)
>> I receive the following error
>> Cannot specify an index or locking hint for a remote
data
>> source.
>> If I remove the () around nolock the statement runs.
>> The question(s) I have
>> 1. Did I set something up wrong?
>> 2. Without the () is the nolock still performing the
same
>> function?
>> 3. Assumining I did not error on the configuration, Why
do
>> I receive this error now, and not before?
>> 4. Any ideas to fix the problem without removing the ()?
>> Thanks in advance for any suggestions/ideas.
>> John Williams
>
>.
>|||Thanks,
That an excellent point, so I am going to get unwanted
results by removing the (), I am concerned about locking
though, do you know since I cannot specify the nolock
hint, am I getting the benefit of (nolock) somehow?
>--Original Message--
>Without the brackets around nolock, the SQL query engine
is assigning the
>word 'nolock' as an alias to your table in the select
statement (i.e.
>equivalent to 'as [nolock]').
>R
>"John Williams" <anonymous@.discussions.microsoft.com>
wrote in message
>news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
>> We are migrating from sql 7 to sql 2000 and for the time
>> being leaving the database in sql 7 compatabiliy mode.
>> Some of the stored procedures reference the tables on a
>> linked server. The linked server is running sql 2000 and
>> the database is sql 2000.
>> When I run the following command
>> select * from linked_server.database.dbo.tablename
(nolock)
>> I receive the following error
>> Cannot specify an index or locking hint for a remote
data
>> source.
>> If I remove the () around nolock the statement runs.
>> The question(s) I have
>> 1. Did I set something up wrong?
>> 2. Without the () is the nolock still performing the
same
>> function?
>> 3. Assumining I did not error on the configuration, Why
do
>> I receive this error now, and not before?
>> 4. Any ideas to fix the problem without removing the ()?
>> Thanks in advance for any suggestions/ideas.
>> John Williams
>
>.
>|||Yes, I think you will find that if you create a stored procedure on the
remote server, instead of running an ad hoc query locally, you will have
more freedom over transactions within the proc, locking hints, etc. You
should always be using stored procs as opposed to ad hoc statements
anyway...
--
http://www.aspfaq.com/
(Reverse address to reply.)
<anonymous@.discussions.microsoft.com> wrote in message
news:274bb01c46371$b02b52a0$a501280a@.phx.gbl...
> I am not sure if I understand what you are asking? Are you
> suggesting that the stored procedure we run on the server
> that has the syntax.
> Select & from linkerserver.database.dbo.table1 and instead
> run a stored procedure on the linked server that
> accomplishes the same thing? I can look into this as an
> option.
> Thanks...
>
> >--Original Message--
> >Can you not create a stored procedure on the remote data
> source that
> >specifies the hint?
> >
> >--
> >http://www.aspfaq.com/
> >(Reverse address to reply.)
> >
> >
> >
> >
> >"John Williams" <anonymous@.discussions.microsoft.com>
> wrote in message
> >news:27f3501c4636b$941fa990$a301280a@.phx.gbl...
> >> We are migrating from sql 7 to sql 2000 and for the time
> >> being leaving the database in sql 7 compatabiliy mode.
> >> Some of the stored procedures reference the tables on a
> >> linked server. The linked server is running sql 2000 and
> >> the database is sql 2000.
> >>
> >> When I run the following command
> >> select * from linked_server.database.dbo.tablename
> (nolock)
> >>
> >> I receive the following error
> >> Cannot specify an index or locking hint for a remote
> data
> >> source.
> >>
> >> If I remove the () around nolock the statement runs.
> >>
> >> The question(s) I have
> >>
> >> 1. Did I set something up wrong?
> >> 2. Without the () is the nolock still performing the
> same
> >> function?
> >> 3. Assumining I did not error on the configuration, Why
> do
> >> I receive this error now, and not before?
> >> 4. Any ideas to fix the problem without removing the ()?
> >>
> >> Thanks in advance for any suggestions/ideas.
> >>
> >> John Williams
> >
> >
> >.
> >