Configuring SQL Linked Server for MySQL

Hello,

I am trying to create a linked server within SSMS for MySQL. I am getting an error message:

I've tried creating my Linked Server as follows:

USE [master]
GO

/****** Object:  LinkedServer [MYSQL]    Script Date: 6/20/2015 11:02:18 AM ******/
EXEC master.dbo.sp_dropserver @server=N'MYSQL', @droplogins='droplogins'
GO

/****** Object:  LinkedServer [MYSQL]    Script Date: 6/20/2015 11:02:18 AM ******/
EXEC master.dbo.sp_addlinkedserver @server = N'MYSQL', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'MySQLODBCANSI'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'MYSQL',@useself=N'False',@locallogin=NULL,@rmtuser=N'root',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'MYSQL', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO



What other steps must I take ?

I'm using SQL Server 2014, MySQL 5.6.25

Thank you,

June 20th, 2015 2:13pm

1. If you use the 32-bit driver on a 64-bit machine, please run the 32bit version of "odbcad32.exe" by running "c:\Windows\SysWOW64\odbcad32.exe"  from Start/Run menu and create your ODBC DSN. However, for the 64-bit driver, use c:\windows\system32\odbcad32.exe to create your ODBC DSN. For more details, please review this similar thread.

2. Expand the folder Providers under Linked server directory in SSMS and go to properties of MSDASQL provider and configure it properly as described in this blog: Creating Linked server to MYSQL from SQL Server.  Refer

Please Mark Answer if it solved your issue, Vote As Helpful if it helps to solve your issue


  • Edited by Dinesh Vishe 12 hours 31 minutes ago
  • Marked as answer by vsla 10 hours 3 minutes ago
Free Windows Admin Tool Kit Click here and download it now
June 20th, 2015 2:36pm

Fantastic!

That was tricky and a bit of a struggle!

Thank you so very much!!!

June 20th, 2015 5:08pm

Hi Dinesh,

While the test connection succeeded, I am not able to select any data from the tables, nor can I insert data into the tables. I am getting an error similar to the following:

contains no columns that can be selected or the current user does not have permissions on that object.


GO

I can see the tables in the database, but I cannot open in MS SQL Server Mgmt Studio.

Any ideas ?

Thank you,


Free Windows Admin Tool Kit Click here and download it now
June 20th, 2015 10:01pm

Ok, yes, I see ... It looks like I must use OpenQuery, there isn't another way ?

e.g.

insert

OPENQUERY(mysql,'select owner_id, name from calendar;')


values

(1,'US')


June 20th, 2015 10:11pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics