joining table on different server

[elisa]

hi,

I would like to join table1 and table2 using a select like this:

SELECT * FROM database1.table1 INNER JOIN database2.table2 ON ...

but database 1 is on a local server and database2 is on a remote server (with different IP address).
Is it possible?
Thanks.

[ccalender]

I think you will need to bring the data from one machine to the other and store it in a temporary table. Then perform the join.

I know it is a bit cumbersome and redundant but I do not think there is a way yet to do this in mysql.

[magelatt]

We do it here all the time. Check out sp_addlinkedserver on BOL.

Once you have a Linked Server set up, you can query it by fully qualifying the join.

EX: SELECT t1.CustID, t1.Company, t2.BalanceDue
FROM vw_CustList t1
INNER JOIN ACCTSVR01.BILLING.dbo.vw_CustBalances t2
ON t1.CustID = t2.CustID
WHERE t2.BalDue > 5000

In the above example, ACCTSVR01, would be the Linked Server that you created.

Caution, using Linked Servers can be a resource hit on your box. So, if you have a long running query you may want to DTS the data to a location on your local server then perform the Query.

If it doesn't have to be "live" you can schedule the DTS early in the morning and work all day on a copy of last nite's data.

[ccalender]

Is this on a MySQL database?

If so, could you explain how you set up a linked server and any other pertinent information?

I would greatly appreciate any information you have to offer.

Thanks in advance!

[magelatt]

Ooops... my bad.

Wasn't paying attention to the forum I was lurking in. NO this is not for MySql. I was referring to MS SQL.

Sorry for any confusion. I'm new here.. still groping along the walls to find my way into the right room.

Mark Gelatt