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 12»»

Linked server using MySQL ODBC 5.1.8 Expand / Collapse
Author
Message
Posted Saturday, December 11, 2010 3:17 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 5, 2012 1:25 AM
Points: 11, Visits: 43
Hi ,

I am trying to add a linked server using the MySQL ODBC 5.1.8 but I am always getting the same error.

OLE DB provider "MSDASQL" for linked server "MySQL" returned message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MySQL".


All documentation i am able to find are of older version of ODBC where there is an advance button for configuration during DNS which is not found on this version.

Does anyone have any experience with this driver.
Post #1033291
Posted Saturday, December 11, 2010 12:29 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 1:11 PM
Points: 12,905, Visits: 32,161
i have this saved in my snippets;
it's from a real comprehensive thread here:

http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx

it's a stored procedure someone contributed to help set up a mysql linked server, along with examples on how to connect:
--http://www.sqlservercentral.com/Forums/Topic340912-146-1.aspx
CREATE PROC uspCreateLinkToMySQLDB @linkedservername varchar(50), @mysqlip varchar(50), @dbname varchar(100), @username varchar(50), @password varchar(50) AS
--@linkedservername = the name you want your linked server to have
--@mysqlip = the ip address of your mysql database
--@dbname = the name of the mysql database you want to operate against. Without this, some of the features of openquery fail
--@username = the username you will use to connect to the mysql database
--@password = the password used by your username to connect to the mysql database

BEGIN
--DROP THE LINKED SERVER IF IT EXISTS
IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = @linkedservername)
EXEC master.dbo.sp_dropserver @server=@linkedservername, @droplogins='droplogins'

--ADD THE LINKED SERVER
DECLARE @ProviderString varchar(1000)

Select @ProviderString = 'DRIVER={MySQL ODBC 5.1 Driver};SERVER=' + @mysqlip + ';Port=3306;OPTION=3;DATABASE=' + @dbname + ';'

EXEC master.dbo.sp_addlinkedserver
@server=@linkedservername,
@srvproduct='MySQL',
@provider='MSDASQL',
@provstr=@ProviderString

EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'collation compatible', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'data access', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'rpc', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'rpc out', @optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'connect timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'collation name', @optvalue=null
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'lazy schema validation', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=@linkedservername, @optname=N'use remote collation', @optvalue=N'true'
EXEC sp_addlinkedsrvlogin @linkedservername, 'false', NULL, @username, @password
END



Once you have the Linked Server in place I have found a few different ways of interacting with the mysql database:

Select Statements:
select * from mysql5...country
select * from OPENQUERY(mysql5, 'select * from country')

Insert Statements:
insert mysql5...country(code,name)
values ('US', 'USA')
insert OPENQUERY(mysql5, 'select code,name from country;')
values ('US', 'USA')

Other Statements:
EXEC('truncate table country') AT mysql5;


Resources:
http://213.136.52.24/bug.php?id=39965
/*
works for me with openquery, but not 4 part naming convention
*/



Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1033360
Posted Saturday, December 11, 2010 7:16 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Friday, October 10, 2014 12:35 PM
Points: 413, Visits: 225
Is your environment 32 or 64 bit?
Post #1033413
Posted Saturday, December 11, 2010 11:41 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 5, 2012 1:25 AM
Points: 11, Visits: 43
it is 32 Bit
Post #1033431
Posted Sunday, December 12, 2010 1:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 6,473, Visits: 13,927
do you have a suitable driver installed on the server, check ODBC settings for the currently installed driver versions

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1033438
Posted Monday, December 13, 2010 2:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:42 PM
Points: 205, Visits: 1,015
hey have u installed mysql odbc drives on your server..................
Post #1033649
Posted Monday, December 13, 2010 2:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 5, 2012 1:25 AM
Points: 11, Visits: 43
Yes i have installed ODBC MySQL driver 5.1.8 but the issue was I was not able to find any documentation on this new version of the driver and hence the configuration are a bit messed up.
Post #1033651
Posted Monday, December 13, 2010 3:06 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, November 5, 2012 1:25 AM
Points: 11, Visits: 43
Sorry , guys ... I was trying to connect to the server and had the ODBC on my machine i.e the client instead of the server just did it on the server and it worked fine.

Thanks for the help.
Post #1033664
Posted Monday, December 13, 2010 7:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 6:47 AM
Points: 6,473, Visits: 13,927
nikshepmehra (12/13/2010)
Sorry , guys ... I was trying to connect to the server and had the ODBC on my machine i.e the client instead of the server just did it on the server and it worked fine.

Thanks for the help.




-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1033776
Posted Wednesday, October 24, 2012 10:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 31, 2014 4:07 AM
Points: 1, Visits: 18
Thank you Lowell :) Your code worked for me.
Post #1376576
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse