How to Create Linked Server for a MySQL database using SQL Server Management Studio

  • Jim Dillon-291542

    Hall of Fame

    Points: 3344

    It took me about a day to figure this out, so I thought I'd try to save someone else the pain...

    Creating a Linked Server in SSMS for a MySQL database

    1. Download the MySQL ODBC driver from mysql.com

    2. Install MySQL ODBC driver on Server where SQL Server resides

    -Double Click Windows Installer file and follow directions.

    3. Create a DSN using the MySQL ODBC driver

    Start-> Settings -> Control Panel -> Administrative Tools -> Data Sources (ODBC)

    -Click on the System DSN tab

    -Click Add

    -Select the MySQL ODBC Driver

    -Click Finish

    On the Login Tab:

    -Type a descriptive name for your DSN.

    -Type the server name or IP Address into the Server text box.

    -Type the username needed to connect to the MySQL database into the user text box.

    -Type the password needed to connect to the MySQL database into the password text box.

    -Select the database you'd like to start in.

    On the Advance Tab:

    Under Flags 1:

    -Check Don't Optimize column width.

    -Check Return Matching Rows

    -Check Allow Big Results

    -Check Use Compressed protocol

    -Check BIGINT columns to INT

    -Check Safe

    Under Flags 2:

    -Check Don't Prompt Upon Connect

    -Check Ignore # in Table Name

    Under Flags 3:

    -Check Return Table Names for SQLDescribeCol

    -Check Disable Transactions

    Now Test your DSN by Clicking the Test button

    4. Create a Linked Server in SSMS for the MySQL database

    SSMS (SQL Server Management Studio -> Expand Server Objects

    -Right Click Linked Servers -> Select New Linked Server

    On the General Page:

    -Linked Server: Type the Name for your Linked Server

    -Server Type: Select Other Data Source

    -Provider: Select Microsoft OLE DB Provider for ODBC Drivers

    -Product name: Type MySQLDatabase

    -Data Source: Type the name of the DSN you created

    On The Security Page

    -Map a login to the Remote User and provide the Remote Users Password

    -Click Add under Local server login to remote server login mappings:

    -Select a Local Login From the drop down box

    -Type the name of the Remote User

    -Type the password for the Remote User

    5. Change the Properties of the Provider MSDASQL

    Expand Providers -> Right Click MSDASQL -> Select Properties

    -Enable Nested queries

    -Enable Level zero only (this one's the kicker)

    -Enable Allow inprocess

    -Enable Supports 'Like' operator

    6. Change settings in SQL Server Surface Area Configuration for Features

    -Enable OPENROWSET and OPENDATASOURCE support.

    7. Change settings in SQL Server Surface Area Configuration for Services and Connections

    -Enable Local and Remote connections via TCP/IP and named pipes

    8. Stop SQL Server and SQL Server Agent

    9. Start SQL Server and SQL Server Agent

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    Have you thought about fleshing this out a bit more, including some of the issues you faced, and submitting as an article? We see this question every so often in the forums and it would be nice to point to the article as a reference. Plus, it's resume building material.

    K. Brian Kelley
    @kbriankelley

  • Jim Dillon-291542

    Hall of Fame

    Points: 3344

    I'd be honored to "flesh it out" some more and submit it as an article. How might I go about this, and what would I "flesh out"?

    Thanks,

    jim

  • K. Brian Kelley

    SSC Guru

    Points: 114455

    Here is the information on how to write for SSC. It's pretty basic, and Steve tends to ensure the articles go in clean and that sort of thing. He's cleaned up some of the articles I've submitted in the past.

    http://www.sqlservercentral.com/other/writeforus.asp

    As to what to flesh out... maybe writing down in paragraph form some of the gotchas you ran into. Also, a screen shot or two showing a tricky point in the configuration usually goes over well. Basically, ask yourself the question, "If I was reading an article on this, where might I want to see a picture of what the author is talking about?" That's been the general rule I've applied whenever I've written an article for SSC.

     

    K. Brian Kelley
    @kbriankelley

  • Jim Dillon-291542

    Hall of Fame

    Points: 3344

    Thanks, I'll see what I can do.

    jim

  • smorozov

    SSC Enthusiast

    Points: 182

    Very helpful, thank you very much!

    Sergey

  • huskerwendy

    Hall of Fame

    Points: 3633

    These instructions for creating a linked MySQL server were great!

    I have a couple more questions concerning querying the MySQL tables. I am wondering if I have to use the OpenQuery function in order to query the MySQL database. The OpenQuery function does not allow variables to be used in its arguments and what I need to do is write a stored procedure that calls the MySQL database and gets the data for a certain record or records and inserts it into the SQL Server database tables. I have found that I can create a view of the whole MySQL table in SQL Server and then use the view in the stored procedures to insert into the SQL Server tables, but I'm wondering if that is my only option. I also tried writing a query to select using the four part identifier, but that is not working either. It's probably because there is no owner for the MySQL tables, but I'm really just guessing on that one.

    I appreciate any help you can provide.

    Thanks!


    Wendy Schuman

  • huskerwendy

    Hall of Fame

    Points: 3633

    I figured out how to do this using variables. I used the sp_executesql and was able to return the new identity so that I can use it throughout my stored procedure.

    DECLARE @SQLStatement nvarchar(max)

    DECLARE @ID as nvarchar(200)

    DECLARE @NewID as int

    SET @ID = 10

    SET @SQLStatement = 'INSERT INTO SQLServerTableName(Col1, Col2, Col3, Col4, Col5)

    SELECT * FROM OPENQUERY(LinkedServerName, ''SELECT Col1, Col2, Col3, Col4, Col5 FROM MySQLTableName WHERE ID = ' + Cast(@ID as varchar)+''' ) '

    SET @SQLStatement = @SQLStatement + ' SELECT @NewID = SCOPE_IDENTITY()'

    EXECUTE sp_executesql @SQLStatement, N'@NewID INTEGER OUT', @NewID OUT


    Wendy Schuman

  • Frank Hunt

    SSC Enthusiast

    Points: 172

    Hey Jim,

    You are awesome man.  This helped me greatly. 

  • TARIK-489611

    Valued Member

    Points: 71

    Thanks.

    This helped me a lot.

  • tembotxus

    Old Hand

    Points: 381

    The above article has been very helpful and has helped me to successfully implement a linked server.

    However, has anyone gotten the 4-part naming convention to work with mysql? instead of using OpenQuery() ?

    eg. Select * from mysqlsrv.mysqldb.owner.table

    if so, can you add some explanatory comments to this post?

    Thanks,

    Joe

  • Jim Dillon-291542

    Hall of Fame

    Points: 3344

    I'm pretty sure I had it working, but maybe with a two part name ie.

    LinkedServerName..TableName

    However, when I went to look yesterday afternoon I had removed all of the mysql Linked Servers I was using for the project. I'll try to make some time today to look into it again. Sorry for not having the answer on the top of my head.

    Thanks,

    jim

  • tembotxus

    Old Hand

    Points: 381

    Jim,

    Thanks a lot for kickstarting me.

    I got it to work using the following!

    eg. Select * from LinkedServerName...TableName

    Your example was close, but just needed the extra period.

    You can't imagine how much code that saves me and having to use temp tables.

    Very cool.

    Thanks,

    Joe

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Thanks! This just came in handy where I work!

    😎

  • Lynn Pettis

    SSC Guru

    Points: 442118

    Just thought I'd add an update. In setting up the linked servers I found that I didn't need to enable Named Pipes on SQL Server 2005, the ODBC connection works fine using TCP/IP (at least for us here).

    😎

Viewing 15 posts - 1 through 15 (of 129 total)

You must be logged in to reply to this topic. Login to reply