cannot specify an index or locking hint.

Hi,

When i use four part object name (servername.database.owner.table)in SELECT
statment with locking hint on SQL2K the error encountered is

"Cannot specify an index or locking hint for a remote data source."

Example : SELECT * from SERVER.pubs.dbo.authors with (nolock)

where as the same query works on SQL7. The server specified in the select
statement may be local server or remote server name. Pls suggest.


Thanks



MNK.

In my case linked server is not involved. I am refering to local server
in four part object name format as a general standard in all my select
statement.

Thanks

MNK.

Well, when you use a four part name, SQL server seems to consider that as a remote data source. If you are running the query on a local source, you may want to remove the SERVER part from the object name.

Hi,

Thanks for your inputs. But in the given scenario it may be difficult
to implement the suggestion

1)use dbname
go
select ..... statement

Incase of a join query the above may not work out.

2)To remove the SERVER part from the object name.

I maintain a master table named dbserverinfo with following structure
ServerName char(20),
DatabaseName char(20)

For every database i query i need to refer the corresponding servername
in dbserverinfo (this is maintained for flexibility purpose). The servername
for the corresponding database may be local or remote server. This is a basic
reason for using four part object name.

Thanks


MNK.

SQL2K did tighten up some of the syntax checking. It sounds like you've been caught by one of them...

The only way to get around the problem you have is to get rid of the server name when the query is local.

How is the query built? Could the select that retrieves the servername, databasename perhaps be changed so that it returns null if servername = @@servername?

Cheers
Twan

Hi,

Thanks for ur suggestions. I need to check which of the option best fit's
my scenario with minimal efforts.

Thanks again






MNK.