SQL Server链接服务器配置与跨库查询操作指南

一、连接 MySQL

首先需要确保目标服务器安装了 MySQL 的 ODBC 驱动（如 MyODBC）。

第1步：配置 ODBC 系统数据源

进入 Windows 数据源管理器，创建一个系统DSN。假设选择数据库 test，数据源名称设置为 myDSN。

第2步：创建链接服务器

在 SQL Server 中执行以下脚本，使用 MSDASQL 提供程序连接：

EXEC sp_addlinkedserver @server = 'MySQLTest', @srvproduct='MySQL',
@provider = 'MSDASQL', @datasrc = 'myDSN'
GO
EXEC sp_addlinkedsrvlogin 
@rmtsrvname='MySQLTest',@useself='false',@locallogin='sa',@rmtuser='mysql用户名',@rmtpassword='mysql密码'

第3步：查询数据

由于部分版本的链接服务器实现存在限制，建议使用 OPENQUERY 进行查询：

SELECT * FROM OPENQUERY (MySQLTest , 'select * from 表名')

注意：直接使用四部分名称 SELECT * FROM 链接名.数据库.dbo.表 可能会报错或返回空值，这是该组件的一个常见 Bug 或限制。此外，OPENQUERY 的第二个参数必须是完整的 SQL 字符串，不能只写表名。

二、连接 Oracle

第1步：创建链接服务器

EXEC sp_addlinkedserver '别名', 'Oracle', 'MSDAORA', '服务名'
GO
EXEC sp_addlinkedsrvlogin @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='oracle用户名',@rmtpassword='密码'

第2步：四部分名称查询

可以直接使用标准的四部分名称，但所有标识符（服务器名、用户名、表名）建议保持大写以避免大小写敏感问题：

SELECT * FROM 别名..用户名.表名

第3步：调用存储过程

使用 OPENQUERY 语法调用远程存储过程：

SELECT * FROM OPENQUERY(别名, 'exec 用户名.存储过程名')

三、设置链接服务器以访问格式化文本文件

利用 Microsoft Jet OLE DB Provider 可以直接查询目录中的文本文件（如 .txt, .csv）。

第1步：准备 schema.ini 文件

在与文本文件相同的目录下创建一个 schema.ini 文件，用于定义文本文件的列结构和数据类型。如果没有此文件，Jet 引擎可能无法正确解析数据。

第2步：创建链接服务器

-- 创建指向包含文本文件目录的链接服务器
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0', 'c:/data/distqry', NULL, 'Text'
GO
-- 设置登录映射（根据实际需求调整Admin账户）
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, NULL, Admin, NULL
GO

第3步：查询数据

链接文本文件时，表名通常用方括号括起来，且文件名中的点号可能需要转义（如 #）：

SELECT * FROM txtsrv...[file1#txt]

四、连接其他 SQL Server 实例

连接同构的 SQL Server 有两种主要方式，推荐使用第二种性能更优。

第1种方法：使用 ODBC 提供程序 (MSDASQL)

适用于异构环境，需指定驱动程序和连接字符串：

EXEC sp_addlinkedserver '别名','','MSDASQL',NULL,NULL,'DRIVER={SQL Server};SERVER=远程IP;UID=用户;PWD=密码;'
EXEC sp_addlinkedsrvlogin @rmtsrvname='别名',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'

第2种方法：使用 SQLOLEDB 提供程序（推荐）

针对 SQL Server 优化的原生提供程序：

EXEC sp_addlinkedserver @server='S1_instance1', @srvproduct='',@provider='SQLOLEDB', @datasrc='S1/instance1'
EXEC sp_addlinkedsrvlogin @rmtsrvname='S1_instance1',@useself='false',@locallogin='sa',@rmtuser='sa',@rmtpassword='密码'

操作示例

建立连接后，即可执行标准的跨库操作：

• 查询：SELECT * FROM 别名.库名.dbo.表名
• 插入：INSERT 本地库.dbo.表 SELECT * FROM 别名.库名.dbo.表
• 新建表：SELECT * INTO 新表 FROM 别名.库名.dbo.旧表

注意事项

在使用链接服务器进行写入操作时需注意以下限制：

1. 自增列限制：若目标表有 Identity 列，需先设置 SET IDENTITY_INSERT ... ON，但这通常无法通过链接服务器直接传递上下文，可能导致插入失败。
2. SELECT INTO：同样的原因，通过链接服务器执行 SELECT * INTO 创建包含自增列的新表时可能会有异常。

五、连接 Access 数据库

使用 Jet OLE DB Provider 连接 .mdb 文件。

EXEC sp_addlinkedserver 
@server = 'test', 
@provider = 'Microsoft.Jet.OLEDB.4.0', 
@srvproduct = 'OLE DB Provider for Jet',
@datasrc = 'C:/Northwind.mdb'
GO

查询语法：

SELECT * FROM test...表名

注：较新版本的 Office/Access (.accdb) 可能需要安装专门的 Access Database Engine 并更换为 ACE 提供程序。

六、连接 Sybase

需要在 SQL Server 服务器上安装 Sybase 客户端或相应的 ODBC 驱动。

方法一：直接使用 ODBC DSN

EXEC sp_addlinkedserver 'Sybase1', ' ', 'MSDASQL', NULL, NULL, 'Driver={Sybase System 11};Database=hisdb;Srvr=10.211.135.12;UID=sa;PWD=1111;'

查询：

SELECT * FROM Sybase1.hisdb.dbo.table1

方法二：配置 Windows ODBC 后再映射

1. 在 Windows 中配置系统 DSN，例如命名为 SYBASETEST。
2. 在 SQL Server 中新建链接服务器：

EXEC sp_addlinkedserver 'SybaseAlias', 'SYBASE ADS', 'SYBASE ASE ODBC Driver', 'SYBASETEST' -- 具体驱动名称依安装版本而定
-- 或使用通用ODBC接口
EXEC sp_addlinkedserver 'SybaseAlias', '', 'MSDASQL', 'SYBASETEST'

配置完成后，可在企业管理器或查询分析器中使用 [链接名]..[用户].[表名] 格式进行访问。

清理与维护

删除不再使用的链接服务器及其登录映射，避免安全隐患：

EXEC sp_droplinkedsrvlogin 'LinkServerName', 'LocalLoginUser'
EXEC sp_dropserver 'LinkServerName'

以上就是SQL Server链接服务器技术配置的详细内容，更多关于数据库跨库查询优化及维护的资料请关注本站其它相关文章！