Tuesday, June 29, 2010

MySQL and SQL Server

...actually, how to make the two talk.

Create a Linked Server in SQL Server (using ODBC):



To query (from SQL Server):

select top 5 * from mysqlanalytics...analytics;

select * from mysqlanalytics...session;


Stored procedures aren't available, but you can get around this by writing a view in MySQL to return the data from the stored procedure (and perhaps use a table to get any parameters the stored procedure might need).

mysql> create view ViewSession as
-> select GetSession() as CurrentSession;
Query OK, 0 rows affected (0.06 sec)

mysql> select * from viewsession;
+----------------+
CurrentSession
+----------------+
1
+----------------+



And ViewSession is now available in SQL Server:

select * from mysqlanalytics...ViewSession