Cross Database Joins with Different SQL Servers

Question

Post reply

Cross Database Joins with Different SQL Servers

Deltatuk

SSC Rookie

Points: 48
More actions
May 15, 2007 at 9:41 pm

#103362

We have a couple of Databases that exist on different servers thus preventing us from doing Joins in our SQL calls. Does anyone have any suggestions on how we can efficiently do a join using multiple SQL Servers in .net without replication or linked servers?

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

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

you can add linked servers (sp_addlinkedserver) and use this select method :

Select * linkedServerName.dbName.dbo.tblName t1 inner join l1.db2.dbo.t2 t2 on ...

eirk tsomik Ten Centuries Points: 1032 More actions · Answer 2

If you do not want to create a linked server You can write A ACtive X script with Dynamic properties where you going to include all you connection variabes

Kindest Regards,

Web programmer

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

"Does anyone have any suggestions on how we can efficiently do a join using multiple SQL Servers in .net without replication or linked servers?"

the terms "efficient" and "join using multiple SQL Servers" do not go together. cross server joins are not efficient.

---------------------------------------
elsasoft.org

eirk tsomik Ten Centuries Points: 1032 More actions · Answer 4

I can only think of 1 method . You need to create 2 connections 1 to your source and another to you destination. Try to dump data in temp tables in this case you would not have to deal with cross server connection and then do the joins on your destination and temp tables

Kindest Regards,

Web programmer

Ed Hammar Ten Centuries Points: 1084 More actions · Answer 5

the terms "efficient" and "join using multiple SQL Servers" do not go together. cross server joins are not efficient.

That's the right answer. This type of design will eventually lead to one extremely slow application and if you don't do a very good job of educating users, developers, and managers, someone will come looking for you to explain why all their employees are angry.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 6

You're facing two problems:

joining across two servers is inefficient (this has already been pointed out)
replicating the entire table just before performing the join is also inefficient and may significantly impact the query -- not to mention your network

So, the best alternative may be to maintain a copy of the remote table on the local server (the server on which you perform the query). However, there is a good chance that you don't need all the columns of the remote table. So your local copy need only contain the columns you actually use in the query. I don't know your situation, but this could make a tremendous difference. Then all you have to do is decide how often your local table is updated: at some specified interval or whenever the remote table is modified.

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms