LinkServer

之前一直都只玩过SQLServer之间是LinkServer，今天才搞了下连MySQL。网上看了些文章，找了些差别

SQLServer：

exec sp_addlinkedserver 'mssql_link','','SQLOLEDB','192.168.1.119' 
exec sp_addlinkedsrvlogin 'mssql_lnk','false',null,'sa','9090960' 

SELECT * FROM [mssql_lnk].[DataBase].[dbo].[Client]

INSERT INTO [mssql_lnk].[DataBase].[dbo].[Client] ([ClientID],[ClientName])VALUES ('','')

UPDATE [mssql_lnk].[DataBase].[dbo].[Client] SET [ClientName] = '' WHERE [ClientID]=''

DELETE FROM [mssql_lnk].[DataBase].[dbo].[Client] WHERE [ClientID]=''

 

MYSQL：

exec sp_addlinkedserver 'mysql_link','','MSDASQL','mysql199' 
exec sp_addlinkedsrvlogin 'mysql_link','false',null,'root','9090960'

SELECT * FROM OPENQUERY ([MYSQL199], 'SELECT * FROM sys_schoolinfo')
SELECT * FROM OPENQUERY ([MYSQL199], 'SELECT * FROM sys_schoolinfo where id=1')
SELECT * FROM OPENQUERY ([MYSQL199], 'SELECT max(id) FROM sys_schoolinfo')

UPDATE OPENQUERY([MYSQL199], 'select * from sys_schoolinfo where id=1') set OrderNum=2
UPDATE OPENQUERY([MYSQL199], 'select * from sys_schoolinfo') set OrderNum=3 where id=1

INSERT INTO OPENQUERY([MYSQL199], 'select * from sys_schoolinfo where 1=0')values (0,'光明大学',1,1,1,1);
INSERT INTO OPENQUERY([MYSQL199], 'select name,ordernum,areaid,schooltype,isdelete from sys_schoolinfo where 1=0') values ('光明正式大学',1,1,1,1);

DELETE FROM OPENQUERY([MYSQL199], 'select * from sys_schoolinfo where id=25')

 

 

另外记录一下SQLServer连接MySQL的linkserver的配置方法

下载mysql连接odbc的包，并在MSSQL服务器中安装，我之前下载的是mysql-connector-odbc-8.0.11-winx64.msi

在windows的odbc连接中配置连接驱动

注：必须是系统DSN

输入MYSQL数据库相关信息

注：Data Source Name是上面LinkServer需要的连接名称