SQL Server Performance

cannot specify an index or locking hint.

Discussion in 'T-SQL Performance Tuning for Developers' started by mkarekar, Oct 16, 2003.

  1. mkarekar New Member

    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.
  2. satya Moderator

    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
  3. mkarekar New Member


    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.
  4. ykchakri New Member

    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.
  5. satya Moderator

    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
  6. mkarekar New Member


    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.
  7. Twan New Member

    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
  8. satya Moderator

    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
  9. mkarekar New Member

    Hi,

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

    Thanks again






    MNK.

Share This Page