Creating a linked server ´MySQL to MSSQL´(query the MySQL database without openquery function)

In addition to my previous linked server tutorials, I decided it is time to add MySQL to the linked server series.
In order to have the bug tracking application, Mantis migrated from linux and have it run on a windows environment, I wanted to create a replication between SQL2008 and MySQL, but then I thought, why not try out a virtual linked server again first, to test Mantis isntallation on a Windows based installation, since the online promise of Mantis on a MSSQl environment is not very promising. So today we will create a linked server from MySQl to MSSQL on a windows 2008R 64 bit environment.

Create DNS for MySQL

In order to do so, We first need to install the correct drivers in order to create a ODBC DSN, Just download the drivers from Mysql developers site http://dev.mysql.com/downloads/connector/odbc/ and install them to your database server. DriversIf we see the listed drivers, it means we can create a new DSN, so open up the System DSN tab and ADD a new DSN, you must fill in the correct credentials, for example:ODBC_Connector

Data Source Name: Enter a describing name, so you can see what it does, you might have more linked servers or other connectors running on the same server.
Description: this isnt maditory, but if you want to be more specific, be my guest.
Server: in my case,it’s localhost, as this is a test server and MSSQL and MySQl are on the same server.
Insert username and password, when this is done. The database will display the possible databases you can connect to, in the dropdown.

Click OK and as you can see the System DSN has been added to ODBC.

Create new Linked Server

When this is done, it’s time to open up the MSSQl server and add a new linked server to the Server Objects.4_create_LInkedsname your linked server, I give it the same name as the SystemDSN. And choose the correct provider: Microsoft OLE DB Provider for ODBC Drivers. and datasource equels DSN name.
You need to fill in all the credentials for the provider string, for example:

DRIVER=(MySQL ODBC 5.2 ANSI Driver);SERVER=localhost;PORT=3306;DATABASE=mantisbt; USER=user;PASSWORD=password;OPTION=3;

Note: meaning of OPTION=3 in the MySQL connection string:
Option=1 FLAG_FIELD_LENGHT: Do not Optimize Column Width
Option=2 FLAG_FOUND_ROWS: Return matching rows
Option=3 option 1 and 2 together

Now click OK, this is always the most fun part to me! when it says connection tot the linked server succeeded!

In addition to this, you can enable provider options on the SQLOLEDB, In my case I select the Dynamic Parameter and Allow inprocess.

Now, lets run the test and see if it connects with the databases, as you can see, it connects all the databases available on the MySQL server.

Connection test

But, most important, we can query it directly. Wheeee!

Linked server without OpenQuery function (Tip!)

Maybe you have read other MySQL linked server tutorials before this one and found out that you could only query the mysql database using the openquery() function or maybe that IS  the reason it brought you to this site. Extra, as in extra work, is never fun! With the correct ODBC driver and the right provider options, you can query the MySQl database, just like any other MSSQL database on your MSSQL server. Just follow the tutorial above and don’t forget to  enable the correct provider options. Cheers!

10 Replies to “Creating a linked server ´MySQL to MSSQL´(query the MySQL database without openquery function)”

  • I really hope I’m wrong, but I don’t think that MySQL can be queried directly using these instructions (if at all). The last example that seems to show it being queried directly is actually running against the local SQL Server mantisbt database which is probably a copy of the MySQL database of the same name. As I said, I really hope I’m wrong because I’d love to query MySQL directly from SQL Server – someone please prove me wrong 🙂

  • Ok, I was wrong 🙂 I found that you can query the MySQL table directly like this:
    SELECT * FROM [MYSQL_LINKED]…mantis_user_table
    Caveat: this only works against the schema specified in the ODBC/Linked Server. I would have expected this to work:
    SELECT * FROM [MYSQL_LINKED]..mantisbt.mantis_user_table
    …but it doesn’t. Has anyone else been able to specify the schema?
    I’m using the MySQL ODBC 5.3 drivers on SQL Server 2008 R2 64-bit.

    • If you have trouble querying the MySql, try using the OPENQUERY function and put your statement between parentheses.
      openquery ('your linked server name', 'your query goes here')

      It’s possible to add one database including tables and views per linked server connection, not just schema’s unless you specified them.

      regards,
      Melanie

  • i got these message after every thing work fine
    An invalid schema or catalog was specified for the provider “MSDASQL” for linked server

  • I just did a bunch of testing and it seems like only one database can be queried from the linked server created as the database is specified in the ODBC DSN. Is this true? Or am I missing something? Do I have to create a liked server per MySQL database?

    • HI Steve, you can either try to query the database with the ‘OPENQUERY’syntax or create another DSN and linked server for the different databases. MYSQL Provider doesnt support switching between catalogs at this moment.

      Grtz,
      Melanie

  • You cannot query it directly as the latest picture shows. I also wasn’t able to query it like this:

    SELECT * FROM [Server].[Database].[Table]

    As a workaround, I use openquery. The only thing I need was able to to copy the tables to SQL for my datawarehouse so I create views for every MySQL Table and use these views in SSIS.

    I use openquery, this works fine:

    Example:

    SELECT *
    FROM openquery ([DWH_NL_PERFECTPRICE_UAT_ODBC64], ‘SELECT * FROM service’)

  • TITLE: Microsoft SQL Server Management Studio
    ——————————

    The linked server has been created but failed a connection test. Do you want to keep the linked server?

    ——————————
    ADDITIONAL INFORMATION:

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

    ——————————

    Cannot create an instance of OLE DB provider “MSDASQL” for linked server “MYSQL_LINKED”. (Microsoft SQL Server, Error: 7302)

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=7302&LinkId=20476

    ——————————
    BUTTONS:

    &Yes
    &No
    ——————————

    • try and check the dynamic parameter in linked server settings. If this doesnt work, reinstall the mysql driver and recreate the linked server.

      greetz Melanie

  • I did create link server I can select SQL Query it well done but it’s doesn’t work when I try to insert
    Like this at SQL server 2016 connect mysql
    [EXEC master.dbo.sp_addlinkedserver
    @server = ‘TEST’,
    @srvproduct = ”,
    @provider = ‘MSDASQL’,
    @provstr = ‘DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=myServer;UID=usr;PWD=pwd;’
    GO
    ]

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/d87e2663-7ee6-4087-8c12-6b9226b85a7c/linked-server-to-mysql-can-select-but-not-insertupdatedelete?forum=sqlgetstarted
    http://stackoverflow.com/questions/23805493/inserting-from-ms-sql-server-to-mysql-database

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: