Workbench is a free tool for MySQL but it is collection of issues in working environment. So I came back on SQL Server management studio. But think if our requirement is something like what , data will be at same place in MySQL and we have to access from SQL Server. Then we can use SQL Server's Server object’s Link server. Today I am going to share 7 steps how I have made linked server from SQL Server to MySQL.
1st Ensure that we have MySQL Connector/ODBC 5.2 is
installed. To verify check Control panel, if it is not then download and installed.
3rd Now Add New Data Source Name, Server name,
Port and Credentials. Then Check by Test option, is everything is all right. If all is OK then click on ok and just close this window.
5th Now Create Link server by Filling required information like below: give
appropriate server name, provider as given, Product name, Data Source Name and
Provider string and we can also give catalog. Linked Server name I have given
XXX and Data-source name will be XYZ, which i gave created earlier from ODBC Connection
manager.
Or
we
can also use below script to create Linked Server
USE [master]
GO
/****** Object: LinkedServer [XXX] Script Date: 26-11-2014 15:20:54 ******/
EXEC master.dbo.sp_addlinkedserver @server
= N'XXX', @srvproduct=N'MySQL', @provider=N'MSDASQL', @datasrc=N'xyz', @provstr=N'DRIVER={MySQL ODBC 5.2
Driver};SERVER=xx.xx.1.xx;PORT=3306;DATABASE=xxxxxx; USER=suman;PASSWORD=
Blue@123;OPTION=3;', @catalog=N'xxxx'
/* For security reasons the linked server remote logins
password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'XXX',@useself=N'False',@locallogin=NULL,@rmtuser=N'suman',@rmtpassword='########'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'collation compatible',
@optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'data access',
@optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'dist', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'pub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'rpc', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'rpc out', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'sub', @optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'connect timeout',
@optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'collation name',
@optvalue=null
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'lazy schema validation',
@optvalue=N'false'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'query timeout', @optvalue=N'0'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'use remote collation',
@optvalue=N'true'
EXEC master.dbo.sp_serveroption @server=N'XXX', @optname=N'remote proc transaction promotion', @optvalue=N'true'
7th After that we will see the Linked
server created as below …
8th Now run below query to select record from sql
server to mysql using link server.
select * from openquery(xxx,'select * from DB.TABLE')
#yoyo MySQL Data in SQL Server….
Comments
Post a Comment
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757