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 Wednesday, October 3, 2007 10:01 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:06 AM
Points: 9, Visits: 97
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



Post #406287
Posted Thursday, October 4, 2007 7:58 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'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
Post #406795
Posted Thursday, October 4, 2007 12:52 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 21, 2014 10:06 AM
Points: 9, Visits: 97
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




Post #407035
Posted Friday, October 12, 2007 1:21 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
Thanks! This just came in handy where I work!




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #410178
Posted Friday, October 19, 2007 10:32 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 6:48 PM
Points: 20,734, Visits: 32,499
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).




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #412910
Posted Thursday, December 6, 2007 4:36 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, February 21, 2012 9:36 AM
Points: 4, Visits: 22
Hi there,

Very interesting article. I was attempting to add a linked server and wanted to confirm something.

I did not create a mapped login and instead specified the remote login and password for the option "Be made using this security context".

I can connect successfully - however - when attempting to expand the "catalogs" folder, I receive the error: "Cannot obtain the schema rwoset "DBSCHEMA_CATALOGS" for OLE DB provider "SQL Server" for linked server "(null)". The provider supports the interface, but returns a failure code when it is used"

ANy ideas? I would appreciate any help.


Also, I then attempted to create a login on the SQL Server and then tried to map it - but it gave me an error "Access to the remote server is denied becuase no login-mapping exists."

I created a login on the sql server called "root" and then mapped it to the remote server with username "root" along with password - but no joy.

Any help would be appreciated.

thanks,

KS
Post #430118
Posted Monday, January 21, 2008 4:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 27, 2011 7:55 AM
Points: 1, Visits: 45
Many thanks Mr Dillon. Your advice uses the MySQL 3.51 ODBC driver. Have you found any reason to upgrade to the 5.1 driver? If so, a similar help sheet would be totally welcome!

Regards
Charlie Howard
Post #445407
Posted Tuesday, January 22, 2008 10:11 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, October 15, 2014 9:20 AM
Points: 292, Visits: 773
Jim:

Your post has been incredibly helpful - thanks so much.
I was in meetings yesterday that revolved around integrating live MySQL data from the web with a local MsSQL database. I was scratching my head wondering how that was going to happen, until I came across your article.

Thanks a million!
-Simon Doubt
Post #445979
Posted Friday, February 15, 2008 7:42 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 2:11 PM
Points: 80, Visits: 201
Hi Jim,
Thanks for providing the detailed instructions. But are these instructions for SQL 2005 32 bit or 64 bit. Because I tried it on SQL 2005 64 bit and I still get the following errors -

OLE DB provider "SQLNCLI" for linked server returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server returned message "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].


Another thing is, when i go to set up the linked server in SSMS, i don't even see the Provider "Microsoft OLE DB Provider for ODBC Drivers" in the drop down list. So i was selecting SQL Native Client as the provider. FYI, I have installed the MySQL 64 bit version driver donwloaded from MySQL site. Please advise if these instructions are for SQL 2005 32 bit or 64 bit?



Post #456296
Posted Friday, February 15, 2008 8:59 AM
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
We had the same problem with our SQL Server 2005 64 bit server. After some research, it seems that Microsoft got rid of their OLE DB Provider for ODBC Drivers. We purchased the OpenLink OleDB Provider for ODBC from Openlink Software for a minimal fee.

http://uda.openlinksw.com/odbc/st/odbc-mysql-st/

http://download.openlinksw.com/download/download.vsp

We had a few issues at first, but their support was great and we didn't even purchase the extra support.



Wendy Schuman
Post #456348
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse