Join tables from two different databases on 2 servers?

[wbodger]

Ok, I have been trying to figure out where to ask this and have not found a good solution, so I will start here and move along if/when needed. So, I have my CRM on SQL 2k and another server running MySQL. What I need to do is open a connection to both of these databases within one query. Is that possible? One is a Windows box and the other is a Unix box.

Oh, it is a web app, but I can call into just one box if it is better to do the join from within one db.

Does that make sense? What I want to do (Basically) is:

select *
from sqlservertbl.field join mysqltbl.field

The code for the query isn't the issue, the issue is really the use of the tables on two disparate servers. Ideas?

wb

 

[bborissov]

SELECT (field list)
FROM [ServerName1].[DataBaseName].[schema name].[TableName] Table1
JOIN
[ServerName2].[DataBaseName].[schema name].[TableName] Table2
 ON Table1.Field = Table2.Field

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[gmmastros]

My suggestion would be to set up a linked server from your SQL2000 box to the mySQL box. Once you have the linked server set up properly, then you can write a qry (executed on the SQL2000 box) that draws data from both databases.

The tricky part (and the part I won't be able to help with) is setting up the linked server. I've done this with other SQL Servers, but never to a mySQL server. I'm 99.9% sure it can be done. For this part, I would suggest a google search, something like [google]SQL Server 2000 linked server setup to mysql[/google].

Once you have the linked server set up, you will want to use the 4 part naming convention with aliases. For example:

Select LocalTable.Col1,
       LocalTable.Col2,
       RemoteTable.ColA,
       RemoteTable.ColB
From   MyTable As LocalTable
       Inner Join LinkedServerName.DatababaseName.SchemaName.TableName As RemoteTable
          On LocalTable.IdColumn = RemoteTable.IdColumn
Where  RemoteTable.ColumnBlah = 'whatever'

Notice that you use the 4 part naming convention to identify the table on the MYSQL server. Give the table an alias, and then reference the alias everywhere else in the query.

Hope this helps.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[wbodger]

Oh, I didn't think I could link to a MySQL box. I will look into that, thank you, gentlemen!

wb