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