6

We're going from a SQL-Server 2008 backend to a MySQL backend. What's the interoperability between SQL-Server and MySQL?

Will we be able to run SQL Queries that reference tables from databases across the servers?

For example, is this possible: pseudo code

SELECT * 
FROM 
      [SQL2008Server].[databaseA].[DBO].[table1] as t1 
  INNER JOIN 
      [MySQLServer].[databaseB].[dbo].[table2] as t2 
          ON t1.id = t2.fkid

If not, what options can you recommend for integrating data across SQL-Server 2008 and MySQL?

Would LINQ provide any relief in regards to combining data from SQL-Server and MySQL?

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
s15199d
  • 7,261
  • 11
  • 43
  • 70

1 Answers1

8

It is possible to add a MySQL server into SQL Server as a linked server.

Once you have set it up you can query using OPENQUERY like this:

SELECT t1.colA, t2.colB
FROM SQLdbName.dbo.tablename AS t1
INNER JOIN OPENQUERY(MySQLlinkedservername, 
                     'SELECT colA, colB FROM tablename') AS t2
ON t1.colA = t2.colA
Mark Byers
  • 811,555
  • 193
  • 1,581
  • 1,452
  • Mark...thank you! That was exactly what I was looking for! This worked beautifully! The sql examples in the tut were a little wonky. So, for anyone searching for it heres an example sql JOIN between our SQL2008Server and MySQLServer: SELECT t1.colA, t2.colB FROM SQLdbName.dbo.tablename as t1 INNER JOIN OPENQUERY(MySQLlinkedservername, 'SELECT colA, colB FROM tablename') as t2 ON t1.colA=t2.colA – s15199d Apr 20 '12 at 20:27
  • @s15199d: Thanks for the comment. I put your SQL from the comment into my answer so that it is easier for others to read. Hope that's OK. – Mark Byers Apr 22 '12 at 07:49