sql 链接服务器 mysql_在sqlserver下增加MYSQL的链接服务器 | 学步园

mssql version:sql server 2008

mysql version:mysql-5.1.32

1,安装MSDASQL

--安装MSDASQL(64 位 OLEDB Provider for ODBC (MSDASQL))

--http://www.microsoft.com/downloads/details.aspx?FamilyID=000364db-5e8b-44a8-b9be-ca44d18b059b&displaylang=zh-cn

--select the version WindowsServer2003.WindowsXP-KB948459-v2-x64-ENU.exe

2,安装mysql

odbc --mysql-connector-odbc-5.1.6-winx64.msi

3,外围应用配置器,注册网络

EXEC

sp_configure 'show advanced option',1

RECONFIGURE

go

exec sp_configure

'Ad Hoc Distributed Queries',1

GO

RECONFIGURE

GO

4,建立MYSQL链接服务器

--用于从mysql获取数据

EXEC

master.dbo.sp_addlinkedserver

@server = N'MYSQL',

@srvproduct=N'MySQL',

@provider=N'MSDASQL', @provstr=N'DRIVER={MySQL ODBC 5.1 Driver}; SERVER=ip; DATABASE=dbname; USER=username; PASSWORD=password; OPTION=3'

SELECT * FROM OPENQUERY(MYSQL,'SELECT id,name FROM test.user');

insert into openquery(MYSQL,'select id,name from test.user where 1=0') values (5,'newUser');

5,建立MSSQL链接服务器

exec sp_addlinkedserver

'MSSQL', '', 'SQLOLEDB', 'tcp:ip\hostname,1433'

go

exec

sp_addlinkedsrvlogin  'MSSQL', 'false',null, 'username', 'password'

go

select * from openquery(MSSQL,'select id,name from test.dbo.[user]')

go