cannot specify an index or locking hint. | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

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.
You should use OPENQUERY if linked server is involed to get the results, refer to books online for more information. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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.
If its on local server, then no need to specify servername.dbname.
Just use as follows: open query analyzer and connect to the server: use dbname
go
select ….. statement
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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
WHen you are connected to the local server and if you specify the servername, you will get the error above. To overcome this issue you must get rid of servername as referred. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Hi, Thanks for ur suggestions. I need to check which of the option best fit’s
my scenario with minimal efforts. Thanks again
MNK.
]]>