Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

How to Create Linked Server for a MySQL database using SQL Server Management Studio Expand / Collapse
Author
Message
Posted Tuesday, January 30, 2007 9:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 7, 2009 7:46 AM
Points: 126, 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

Post #340912
Posted Tuesday, January 30, 2007 7:44 PM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #341127
Posted Wednesday, January 31, 2007 7:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 7, 2009 7:46 AM
Points: 126, 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
Post #341314
Posted Wednesday, January 31, 2007 7:52 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, November 14, 2014 7:14 AM
Points: 6,625, Visits: 1,876

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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #341321
Posted Wednesday, January 31, 2007 8:02 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, October 7, 2009 7:46 AM
Points: 126, Visits: 176
Thanks, I'll see what I can do.
jim
Post #341334
Posted Tuesday, May 1, 2007 3:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, September 22, 2014 7:29 AM
Points: 4, Visits: 243

Very helpful, thank you very much!

Sergey




Post #362449
Posted Tuesday, May 8, 2007 6:21 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 1:22 PM
Points: 961, 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
Post #364246
Posted Wednesday, May 9, 2007 5:41 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, August 10, 2010 1:22 PM
Points: 961, 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
Post #364598
Posted Thursday, July 5, 2007 9:00 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, November 25, 2011 8:08 PM
Points: 60, Visits: 10

Hey Jim,

You are awesome man.  This helped me greatly. 

Post #379420
Posted Monday, September 17, 2007 2:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, December 28, 2007 6:35 AM
Points: 1, Visits: 4

Thanks.

This helped me a lot.

Post #399610
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse