SQL Linked Server to MySQL Problem

• homebrew01

  SSC Guru

  Points: 55549

  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.

• Lowell

  SSC Guru

  Points: 323522

  More actions

  June 21, 2013 at 8:35 am

  #1626350

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

  More actions

  June 21, 2013 at 9:04 am

  #1626365

  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 !