May 13 2007

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 –


EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'MySQL', @provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 3.51 Driver}; SERVER=127.0.0.1; DATABASE=DB_Name; USER=UserName; PASSWORD=Password; OPTION=3'

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

15 comments on “Getting Microsoft SQL Server and mySQL to talk”

  1. Try this:
    INSERT openquery (MYSQL,
    ‘ Select * from ogiblog.insert_test ‘) VALUES (2,’Bob’)

  2. INSERT into openquery(dbserver1MySQL,’select * from graham.lookup_in_table’)
    select * from NavteqAPAC.dbo.Admin_Names

    Gives me the following error

    OLE DB provider “MSDASQL” for linked server “dbserver1MySQL” returned message “[MySQL][ODBC 5.1 Driver][mysqld-5.1.49-community]Commands out of sync; you can’t run this command now”.

  3. I’m getting thsi folloiwng error! Any Idea?

    OLE DB provider “MSDASQL” for linked server “dbserver1MySQL” returned message “[MySQL][ODBC 5.1 Driver][mysqld-5.1.49-community]Commands out of sync; you can’t run this command now”.
    Msg 7343, Level 16, State 2, Line 1
    The OLE DB provider “MSDASQL” for linked server “dbserver1MySQL” could not INSERT INTO table “[MSDASQL]”.

  4. How do you do exactly the opposite?

    Does MySQL provide the same functionality as ‘Linked Servers’? I have a stored procedure in MS SQL environment, which I want to call from MySQL. How can I accomplish this?

    • 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 :-)

  5. Just wait until you have a few million rows in that table and you try to:

    INSERT into openquery(MySQL,’select * from db_name.table_name’)

    Then MSSQL will timeout as it waits for the openquery command to return all of them. I ended up having one of the guys I manage write me a python tool that copies data from MSSQL to MySQL.

    • 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 :-)

  6. 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?

  8. It appears that under MySQL Connector 5.1 (through 5.1.5, at least), INSERT INTO OPENQUERY() doesn’t work. This command worked fine under MySQL Connector 3.51.

  9. It seems like if SQL server 2005 can’t insert data in Mysql :
    I use MySQL ODBC 5.1 Driver to connect to Mysql 5 works fine to select but unable to insert…

  10. You said it man – the fancy graphical user interface is spiffy but when it gets down to it T-SQL is where its at… :-) I’m glad I could at least get you started on the right path.

  11. Nothing like dedication to ones job… :)

    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.. :)

  12. HELP!

    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.

    ???

    Any ideas…

  13. I have been looking for detailed instructions to do exactly this for a very long time. Thanks for the post.

Comments are closed.