一、Sqlserver链接Sqlserver

1.1、创建链接

EXEC master.dbo.sp_addlinkedserver @server = N'HRNEW', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'120.*****'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HRNEW',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='**********'

1.2、select 查询。

如上方创建了名称为“HRNEW”链接，查询语句如下

select * from HRNEW.dbo.User where 1=1

二、Sqlserver链接到Mysql

2.1、安装mysql-connector-odbc-8.0.28-winx64.msi

下载地址：https://dev.mysql.com/downloads/file/?id=509492

点击安装后却提示需要安装visualstudio

如果需要安装 Visual Studio Community 2019请先下载 Visual Studio Community 2019 for Windows x64

Visual Studio Community 2019 for Windows x64

This application requires Visual Studio 2019 x64Redistributable, Please install the Redistributable then runthis installer again.

Latest supported Visual C++ Redistributable downloads | Microsoft Learn

2.2配置数据源

*****特别注意********如果此处的Character Set选择不合理。select的时候可能会返回提醒：无效数据

可参考：https://www.cnblogs.com/gered/p/13883887.html

----查看数据库的character set
SELECT *
FROM information_schema.SCHEMATA

2.3、创建链接服务器

切记：提供程序，必须选择“Microsoft OLE DB Provider for ODBC Drivers”

2.4测试

–查询 
SELECT * FROM OPENQUERY(MYSQL_DBLINK, ‘select * from tableName where id=”1”’)

–修改 
UPDATE OPENQUERY(MYSQL_DBLINK, ‘select * from tableName where id=”1”’) set cname=’测试’ 
–or 
UPDATE OPENQUERY(MYSQL_DBLINK, ‘select * from tableName ‘) set cname=’测试’ where id=1

–添加 
INSERT INTO OPENQUERY(MYSQL_DBLINK, ‘select * from tableName where 1=0’)values (‘xx’,’xx’,’xx’);

---跨数据库联表追加
INSERT INTO OPENQUERY(FINEBIDB, 'SELECT username, fullname, userpass FROM userinfo')
SELECT u.workcode AS username, u.lastname AS fullname, u.password AS userpass
FROM hrmresource u
LEFT JOIN FINEBIDB...userinfo bu ON CAST(bu.username AS varchar(50)) = u.workcode
WHERE u.departmentid = 1
    AND u.Status <= 1
    AND u.seclevel > 20
    AND bu.username IS NULL


–删除 
DELETE FROM OPENQUERY(MYSQL_DBLINK, ‘select * from tableName where id=”1”’)
--跨数据库访问
SELECT u.id,u.lastname,m.workcode,u.passwordlock,lzrq
FROM V_OA_lzcx l
	LEFT JOIN hrmresource u ON l.xm = u.id
	LEFT JOIN OPENQUERY(OAEMP, 'SELECT open_user_id, workcode FROM emp_app.em_cp_user WHERE workcode IS NOT NULL') m
	on u.workcode = m.workcode

打赏赞