SQL Linked Server to MySQL Problem

Question

SQL Linked Server to MySQL Problem

homebrew01

SSC Guru

Points: 55544
More actions
June 21, 2013 at 8:19 am

#274274

I successfully downloaded the MySQL drivers and installed the. There are 2 ... ANSI and Unicode. Both are successful when I do the test connection to MySQL and I can select the MySQL database that I want to use. Is the ANSI driver the correct one ?
I am having trouble getting the linked server set up. I have found various instructions on line, but I must be doing something wrong, because I cannot establish a connection to MySQL
Any good "MySQL Linked Server for Dummies" recommended articles ??
EDIT: Running SQL 2005 64 bit, so I should have posted in SQL 2005 forum.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

I can certainly help here; my example here, at least for me, works fine, except i cannot get 4 part statemetns to work; i only see to get openquery stuff to work for me.

here's a full example, first a proc to easily create linked servers for MySQL:

--#################################################################################################

--Linked server Syntax for SQL Server

--#################################################################################################

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

CREATE PROC sp_CreateLinkedServerToMySQL @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

GO

Then i created my linked server; in this case, i actually created a "sandBox" database/catalog in MySQL prior to doing this:

EXEC sp_CreateLinkedServerToMySQL

@linkedservername ='Linky_To_MySQL',

@mysqlip='127.0.0.1', --Local, so use the loopback

@dbname = 'SandBox', --I actually Created this database

@username='root', --The default mySQL user

@password ='NotTheRealPassowrd' --The Password I changed to at install of MySQL

After that, i use the classic command to get available tables:

EXEC sp_tables_ex Linky_To_MySQL

/*

TABLE_CATTABLE_SCHEMTABLE_NAMETABLE_TYPEREMARKS

sandbox_workspaceTABLE

sandboxallfilenamesTABLE

*/

and some examples of working cod3; i can get openquery stuff to work, but not the 4 part stuff:

select * from OPENQUERY(Linky_To_MySQL, 'select * from allfilenames')

--cannot get this to work?!?!

select * from Linky_To_MySQL...allfilenames

insert Linky_To_MySQL...allfilenames(whichpath,WHICHFILE)

values ('US', 'USA')

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!

homebrew01 SSC Guru Points: 55544 More actions · Answer 2

Thanks, that's helpful

I also found I needed the 64 bit ODBC driver:

http://www.microsoft.com/en-us/download/confirmation.aspx?id=20065

I am now able to query MySQL data from SQL, so I have made progress !