Post reply

Link Servers an Distribution Queries

  • August 24, 2006 at 2:55 pm

    #90358

    I have a link server setup with the following set to true: Collation Compatible, DataAccess, RPC, RPC Out

    The issue is that when running the query below

    SELECT name, address, street FROM table1 WHERE name = Linda

    it resolves the query on the remote server

    but when using a variable the WHERE clause is not resolved on the remote server so the entire table is moves to the local server

    Is there a way or a place to configure sql server to do all the work on the remote server and not on local server when using a variable

     

    Thanks for all your help

     

     

     

     

  • August 25, 2006 at 1:00 pm

    #656913

    try putting it in a stored procedure with a parameter on the remote server.

    then invoke it from your local server.  should run on the remote and return only what you really need.

    exec lnksvr.dbname.dbo.uspYoursp @somevar.

     

  • August 25, 2006 at 4:21 pm

    #656968

    Try using dynamic sql, creating a variable where you store the SQL statement, and then call the xp_executesql.

    Something like this:

     

    sSql = "SELECT name, address, street FROM table1 WHERE name =" + @UserName

    exec sp_executesql @sSql

     

    Hope it helps.

  • August 25, 2006 at 4:42 pm

    #656973

    this will actually create schema locks on the remote server and the local server, since it has no stats on the remote table, will request various table/column stats to be sent back to the local server.  Using the storedprocedure is the more optimal approach.

  • August 28, 2006 at 5:21 am

    #657038

    I did not know this behaviour.

    Thank you Shawn!!

  • August 29, 2006 at 9:50 am

    #657299

    I also found out that if you enable dynamic properties and nested query by creating a new link server and selecting Microsoft OLE DB Provider for sql server will enhance your query performance between link servers.

     

    In the provider Options is where you can enable this properties

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply