How to Create Linked Server for a MySQL database using SQL Server Management Studio
-
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
-
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 -
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
-
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 -
Thanks, I'll see what I can do.
jim
-
Very helpful, thank you very much!
Sergey
-
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 -
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 -
Hey Jim,
You are awesome man. This helped me greatly.
-
Thanks.
This helped me a lot.
-
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
-
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
-
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
-
Thanks! This just came in handy where I work!
😎
-
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 128 total)
You must be logged in to reply to this topic. Login to reply