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 «««910111213»»

How to Create Linked Server for a MySQL database using SQL Server Management Studio Expand / Collapse
Author
Message
Posted Friday, January 6, 2012 12:34 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 17, 2014 11:56 AM
Points: 20, Visits: 252
Thanks Jim, this saved me a LOT of time and aggravation today.

Ken Stelter


No good deed goes unpunished.
Post #1231678
Posted Friday, February 17, 2012 12:12 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 22, 2012 4:09 AM
Points: 6, Visits: 13
As per your steps, I have created the linked server for MySQL in sql server management studio.
I can able to see the database and also the tables.
But i cant able to view the records and also i cant able to view/alter the table columns.
Is this any permission that i need to give.. and where to assign this..?

help plz...
Post #1253636
Posted Friday, February 17, 2012 3:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:22 AM
Points: 298, Visits: 230
the USerid and password you are using to connect to Mysql dont have appropriate permission
Post #1253710
Posted Friday, February 17, 2012 3:12 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 22, 2012 4:09 AM
Points: 6, Visits: 13
how and where to make that user permission in Linked server...?
Post #1253713
Posted Friday, February 17, 2012 3:19 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:22 AM
Points: 298, Visits: 230
This needs to be done at MYSQL server end, i never worked on MYSQL so cannot tell you how to create user or give permission at MYSQL. In my case MYSQL DBA in our company created a USerid and password and handed over to me.
Post #1253721
Posted Friday, February 17, 2012 3:26 AM


Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 22, 2012 4:09 AM
Points: 6, Visits: 13
ya i have mysql userid and password..when i used that credentials i cant able to create lincked server.
so what i do is , i created a new user in sql server and add that user to the linqed server.

i also tried to map the sql server user to mysql server user..its connecting.

but i didnt able to see tables columns...and fetch datas
Post #1253727
Posted Wednesday, April 18, 2012 12:54 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, July 4, 2012 4:47 AM
Points: 1, Visits: 47
Excellent!!!!
Post #1285447
Posted Friday, May 18, 2012 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 18, 2012 12:30 PM
Points: 1, Visits: 0
This was really helpful guys. Thanks. But I am stuck with this error now

OLE DB provider "SQLNCLI" for linked server "MISSERVER" returned message "Login timeout expired".
OLE DB provider "SQLNCLI" for linked server "MISSERVER" 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].

Please HELP !
Post #1302756
Posted Wednesday, July 25, 2012 2:13 PM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Thursday, July 17, 2014 2:01 PM
Points: 687, Visits: 3,002
I recently went to install the ODBC connector and create a linked server to MySQL as described here http://www.sqlservercentral.com/Forums/FindPost1332192.aspx. I ran into and solved 2 problems, so I figured I'd post them here.

1. You get this error when you try to create the DSN on a 64-bit machine connected to a 32-bit server:

The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)

The solution is here http://msdn.microsoft.com/en-us/library/ms712362%28VS.85%29.aspx. Basically, you have to use the WOW64 version of the ODBC editor to create the DSN.

2. Once your DSN and linked servers are created, you can SELECT from the linked server, but you cannot INSERT or UPDATE. You get this error when you try:

OLE DB provider "MSDASQL" for linked server "mylinkedserver" returned message "Row cannot be located for updating. Some values may have been changed since it was last read.".
Msg 7343, Level 16, State 4, Line 1
The OLE DB provider "MSDASQL" for linked server "mylinkedserver" could not UPDATE table "[mylinkedserver]...[mytable]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

This is apparently quite common, and I read at least 3 different fixes for it. The one that worked for me, however, was to edit the Cursors/Results tab of the DSN connection and check the box for "Return matched rows instead of affected rows"

Hope this helps someone else out,
Rich
Post #1335431
Posted Thursday, December 27, 2012 11:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, January 27, 2013 9:24 AM
Points: 1, Visits: 2
Thanks a bunch! This was extremely helpful as I am not very versed in MySQL!
Post #1400712
« Prev Topic | Next Topic »

Add to briefcase «««910111213»»

Permissions Expand / Collapse