December 11, 2010 at 3:17 am
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.
December 11, 2010 at 12:29 pm
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
December 11, 2010 at 7:16 pm
Is your environment 32 or 64 bit?
December 11, 2010 at 11:41 pm
it is 32 Bit
December 12, 2010 at 1:31 am
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" 😉
December 13, 2010 at 2:23 am
hey have u installed mysql odbc drives on your server..................
December 13, 2010 at 2:33 am
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.
December 13, 2010 at 3:06 am
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.
December 13, 2010 at 7:01 am
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" 😉
October 24, 2012 at 10:13 am
Thank you Lowell 🙂 Your code worked for me.
October 24, 2012 at 11:30 am
maria_js (10/24/2012)
Thank you Lowell 🙂 Your code worked for me.
excellent! I'm glad you found this thread and the helpful procedure!
Lowell
February 10, 2013 at 10:30 am
Excellent, worked a treat! thanks
January 25, 2018 at 11:08 am
Just an update for Lowells script. I had to add the datasource for the MySQL ODBC 5.3 Driver. Basic changes are:
--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)
, @datasource 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
-- @datasource = the remote database on the mysql server
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.3 Driver};SERVER=' + @mysqlip + ';Port=3306;OPTION=3;DATABASE=' + @dbname + ';'
EXEC master.dbo.sp_addlinkedserver
@server=@linkedservername,
@srvproduct='MySQL',
@provider='MSDASQL',
@datasrc= @datasource,
@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
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply