Random musings – Sometimes technical – More often not

Main menu

Post navigation

Getting Microsoft SQL Server and mySQL to talk

One of the biggest things I have to do as part of my company’s transition to using mySQL is the migration of the existing data to the mySQL platform. The problem was getting Microsoft’s SQL Server 2005 and mySQL to talk in a straightforward and flexible manner. The main issue is that SQL Server 2005 does not provide ODBC drivers out of the box.

This IMO is a big backward step for SQL Server 2005. If you use the SQL Server Management Studio interface there is .NET ODBC driver available as a data-source when trying to import data but when you try to make ODBC as your data-sink these options are no longer available. You get this issue when using SSIS also so I went on another tack – linked servers.

Linked servers are simply connections to other data sources or data sinks that are maintained by the Microsoft SQL Server. These can be local or remote and are a flexible and powerful way of making data that is spread across multiple servers available. You big advantage is that you could address the data with SQL statements directly as if they were on the same machine. Linked Servers could be ODBC data-sources as long as the appropriate ODBC drivers were available.

So I went to mySQL and got the mySQL ODBC Driver. After installing the driver on the server (mySQL has detailed installation instructions here), setting up the linked server was pretty straightforward –

Now you have set up a linked mySQL server using ODBC. Since this is an ODBC connection selecting and inserting data to and from it is slightly different. You use the openquery function like so –
Selecting Data -
select * from openquery(MySQL, 'select * from `db_name`.`table_name`')
Inserting Data -
INSERT into openquery(MySQL,'select * from db_name.table_name')
select * from db_name.dbo.table_name

First of all let me preface this by saying I haven’t actively worked on mySQL since 5.1 was in beta (when mySQL was still an independent entity) and I am not sure of the state of the art today. AFAIK – I don’t think there is an analogue to the “Linked Servers” in mySQL. However depending on what exactly you are trying to do there is a couple of approaches you can try –

1. If the data that you are trying to get is not very time sensitive, you can set up a job on the Microsoft SQL server side to periodically transport it (the data) to mySQL (via linked server) and then use it (in mySQL). A similar approach would work if you are trying to modify data in Microsoft SQL Server as well.
2. You can use a third party tool – a script using a technology that has access to both databases to act as the bridge between them. You can use the tool to get data from one database to update the other or to run the stored procedure on demand. Running this tool could be done using mySQL user defined functions (UDF) – there is a repository of user defined function available – here.

Hope this helps :-) – Please share your solution – I would be really interested to here what you did :-)

Yeah, A few million rows would be a problem. One could tune the select query to return a range of rows (a few thousand for example) and loop through the million rows. It would depend on the specific circumstance however and in some cases simply using a script is your best bet :-)

Hi,
i’m working with linked Server and everything works fine, but i have a big problem. In my MSSQL i have fields of type money and i want to merge them into the mysql DB – type decimal 13,6 i castet and converted as a devil, but i dont get the values from the MSSQL to MySQL – has someone an idea?

I figured this out with a little headache some asprin and SQL Server Books Online. BTW THANKS for the connection via linked server idea. I was fighting with SSIS and .NET… Nothing like transact SQL Statements to make our lives easier.. :)

I have gotten as far as getting the server to connect via a linked server. I set all the necessary permissions for the connections but keep getting the following error

Invalid use of schema or catalog for OLE DB provider “MSDASQL” for linked server “ECWSOURCE”. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog or schema.