Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked server using MySQL ODBC 5.1.8


Linked server using MySQL ODBC 5.1.8

Author
Message
nikshepmehra
nikshepmehra
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
Lowell
Lowell
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17734 Visits: 39395
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Kory Becker
Kory Becker
SSC-Addicted
SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)SSC-Addicted (413 reputation)

Group: General Forum Members
Points: 413 Visits: 257
Is your environment 32 or 64 bit?
nikshepmehra
nikshepmehra
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 43
it is 32 Bit
Perry Whittle
Perry Whittle
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10928 Visits: 16805
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" ;-)
naresh.talla
naresh.talla
SSC Veteran
SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)SSC Veteran (233 reputation)

Group: General Forum Members
Points: 233 Visits: 1015
hey have u installed mysql odbc drives on your server..................
nikshepmehra
nikshepmehra
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
nikshepmehra
nikshepmehra
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 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.
Perry Whittle
Perry Whittle
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10928 Visits: 16805
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" ;-)
maria_js
maria_js
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 30
Thank you Lowell Smile Your code worked for me.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search