SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Jim Dillon-291542
Jim Dillon-291542
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 176
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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10146 Visits: 1917

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
Jim Dillon-291542
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 176
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
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10146 Visits: 1917

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
Jim Dillon-291542
Old Hand
Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)Old Hand (344 reputation)

Group: General Forum Members
Points: 344 Visits: 176
Thanks, I'll see what I can do.
jim
smorozov
smorozov
Grasshopper
Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)Grasshopper (16 reputation)

Group: General Forum Members
Points: 16 Visits: 253

Very helpful, thank you very much!

Sergey





huskerwendy
huskerwendy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 409
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
huskerwendy
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1065 Visits: 409
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
Frank Hunt
SSC Journeyman
SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)SSC Journeyman (80 reputation)

Group: General Forum Members
Points: 80 Visits: 10

Hey Jim,

You are awesome man. This helped me greatly.


TARIK-489611
TARIK-489611
Grasshopper
Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)Grasshopper (23 reputation)

Group: General Forum Members
Points: 23 Visits: 4

Thanks.

This helped me a lot.


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search