|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Wednesday, October 07, 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
|
|
|
|
|
Keeper of the Duck
Group: Moderators
Last Login: 2 days ago @ 1:55 PM
Points: 6,584,
Visits: 1,789
|
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Wednesday, October 07, 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
|
|
|
|
|
Keeper of the Duck
Group: Moderators
Last Login: 2 days ago @ 1:55 PM
Points: 6,584,
Visits: 1,789
|
|
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
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Wednesday, October 07, 2009 7:46 AM
Points: 126,
Visits: 176
|
|
Thanks, I'll see what I can do. jim
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Monday, November 19, 2012 9:08 AM
Points: 4,
Visits: 188
|
|
Very helpful, thank you very much! Sergey
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
Valued 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.
|
|
|
|
|
Forum Newbie
      
Group: General Forum Members
Last Login: Friday, December 28, 2007 6:35 AM
Points: 1,
Visits: 4
|
|
Thanks. This helped me a lot.
|
|
|
|