how to query MySQL from SqlServer 2K8R2

Question

how to query MySQL from SqlServer 2K8R2

RightOnTarget

Hall of Fame

Points: 3041
More actions
April 10, 2013 at 5:05 pm

#270953

Hi all,
Could someone please share a link or article to help me setup a way to query MySQL database from SqlServer?
Thanks,

Viewing 6 posts - 1 through 5 (of 5 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Have you searched the forums on ssc for MySQL and Linked Servers? I know this has been asked before but I don't remember the specific threads.

Pankaj067 Old Hand Points: 313 More actions · Answer 2

You need to create a linked server for that in sql server.

Also before that you have to create a ODBC Connector

The steps for the same has been described at:

http://sql-articles.com/articles/dba/creating-linked-server-to-mysql-from-sql-server/

Thanks.....:-)

Lowell SSC Guru Points: 323504 More actions · Answer 3

this thread on SSC is what i have saved, and has been working for me:

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

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!

RightOnTarget Hall of Fame Points: 3041 More actions · Answer 4

Thank you for replies. Does it matter if I am running 64bit installation of sql server?

Lowell SSC Guru Points: 323504 More actions · Answer 5

rightontarget (4/15/2013)
Thank you for replies. Does it matter if I am running 64bit installation of sql server?

no;when you install MySQL, both providers(32 bit and 64 bit) are installed. there's a later post that has a stored procedure which does the setup of the linked server details, and mine is working fine in a 64 bit SQL2008.

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

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

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 ='NotTheRealPassword' --The Password I changed to at install of MySQL

EXEC sp_tables_ex Linky_To_MySQL

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!