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

*/