Post reply

JOIN to data on another server

  • December 2, 2005 at 5:05 am

    #111833

    Hi

    I can create joins between two seperate DB's based on the same server without a problem, can I go one step further and create a join between two DB's on seperate server hardware? They're within the same network but the login credentials are different, both are SQL2000 systems.

    Any pointers would be appreciated.

    Thanks,

    Lee

  • December 2, 2005 at 5:27 am

    #607303

    I think it's the OPENQUERY and sp_addlinkedserver I need to look at, I'll post back if I've got any questions.

  • December 2, 2005 at 6:53 am

    #607325

    Finally decided to use OPENROWSET

  • December 2, 2005 at 8:28 pm

    #607564

    You can defined a linked server on ServerA that refers to ServerB, then you can use it more easily than the rowset functions.  Whatever login credentials you need for ServerB can be defined with the linked server.

    select ...

    from db1.dbo.table1 t1

    join db2.dbo.table2 t2 on ...

    join ServerB.db3.dbo.table3 on ...

    You have to be careful and check the query plans.  Sometimes it will pull the entire table over and do filtering at the destination.  I've seen complex queries where it wanted to pull two tables over the network and join them at the destination, where doing the join first would reduce the rowcount transferred.  One sign that it is confused is when it assumes the remote table has a rowcount of exactly 10,000 and you know it's closer to 10 million.  In such cases I fall back on OPENQUERY:

    select ...

    from db1.dbo.table1 t1

    join OPENQUERY(Serverb,

         'SELECT ... FROM db2.dbo.table2 t2

          JOIN db3.dbo.table3 t3 on t2... = t3...

          WHERE ...'

    ) t2 on ...

    You can use a four-part name with INSERT INTO to insert into a remote table, but I've found this to be slow for large amounts of data.  I have had to put the insert in a stored proc on ServerB, then (on ServerA) use "exec ServerB.db..RemoteInsertProc" to call it.

  • December 5, 2005 at 2:37 am

    #607630

    Hi Scott

    Thanks for the tips, much appreciated.

    I'm going to go back and work on the linked server thing, I resorted to OPENROWSET because I couldn't get the linked server and OPENQUERY working properly, even though all credentials were correct.#

    Again, thanks Scott

  • December 5, 2005 at 4:10 am

    #607638

    Got it working, (linked server that is) very fast it is too!

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

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