SQL Server创建类似ORACLE数据库DBLINK数据间资料传递方法

-- 查询我的SQL Server 的版本信息

SELECT
@@VERSION AS 'SQL Server and OS Version',
SERVERPROPERTY('ProductVersion') AS 'Product Version',
SERVERPROPERTY('ProductLevel') AS 'Service Pack Level',
SERVERPROPERTY('Edition') AS 'Edition',
SERVERPROPERTY('EngineEdition') AS 'Engine Edition';

　-- 查询有多少链接服务器记录

 SELECT * FROM sys.sysservers where is_linked = 1;  

/*SQL Server and OS Version: 包含了 SQL Server 的版本、构建编号以及操作系统的信息。
Product Version: 显示具体的 SQL Server 版本号。
Service Pack Level: 表示安装的服务包级别（如果有）。
Edition: SQL Server 的版本类型，如 Enterprise, Standard 等等。
Engine Edition: 提供引擎版本的一些基本信息，比如是否是个人版、标准版、企业版等。
例如，输出可能看起来像这样：
SQL Server and OS Version: Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) Sep 24 2019 13:48:23 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 18363: )
Product Version: 15.0.2000.5
Service Pack Level: RTM
Edition: Developer Edition (64-bit)
Engine Edition: 3

其中 Product Version 的解释如下：

主版本号：如 15. 对应 SQL Server 2019
次版本号和修订号：.0.2000.5 提供了更详细的构建信息

*/

1.SQLSERVER-SQLSERVER
-- 1. 添加链接服务器

EXEC sp_addlinkedserver
@server = N'JFNBLABEL_SERVER', -- 自定义的链接服务器名称（可任意命名）
@srvproduct = N'MSSQL', --链接工具
@provider = N'SQLNCLI11', -- 使用 SQL Server Native Client 11.0 OLE DB 提供程序 推荐使用 Microsoft OLE DB Driver for SQL Server (MSOLEDBSQL)，它比旧版的 SQLNCLI11 更现代、更安全。MSOLEDBSQL要求是较新的 SQL Server 版本（如 SQL Server 2016 SP2+ 或 SQL Server 2017+）
@datasrc = N'192.168.1.1,1433', -- IP 和端口 SQL SERVER默认端口是1433
@catalog = N'TEST'; -- 默认数据库名

 

-- 2. 设置登录映射（使用特定用户名和密码）

EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'JFNBLABEL_SERVER',
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sa', -- 替换为实际的远程 SQL 登录用户名
@rmtpassword = N'111111'; -- 替换为对应的密码

 

-- 3. 可选：设置链接服务器选项（推荐）

EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'collation compatible', @optvalue=N'true';
EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'data access', @optvalue=N'true';
EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'dist', @optvalue=N'false';
EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'pub', @optvalue=N'false';
EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'sub', @optvalue=N'false';
EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'rpc', @optvalue=N'false';
EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'rpc out', @optvalue=N'true';
EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'connect timeout', @optvalue=N'30';
EXEC sp_serveroption @server=N'JFNBLABEL_SERVER', @optname=N'query timeout', @optvalue=N'60';

 

/*
collation compatible: 如果设置为 true，则假定远程服务器使用与本地服务器相同的排序规则，从而避免某些字符比较时出现的问题。
data access: 启用或禁用数据访问功能。如果设置为 true，则可以从本地服务器查询远程服务器上的数据。
dist: 是否启用作为分发者的功能。通常不需要开启。
pub: 是否启用作为发布者的功能。同样地，通常不需要开启。
sub: 是否启用作为订阅者的功能。如果你不使用复制服务，可以设为 false。
rpc: 允许从本地服务器到远程服务器的远程过程调用。
rpc out: 允许从远程服务器到本地服务器的远程过程调用。
connect timeout: 设置连接超时时间，单位为秒。
query timeout: 设置查询超时时间，单位为秒。
*/

-- 查询远程数据库中的某个表

SELECT top 5 * FROM [JFNBLABEL_SERVER].[TEST].[dbo].[item_table];

-- 查询语法 SELECT * FROM [链接服务器名].[数据库名].[架构名].[表名]

 2.SQLSERVER-ORACLE

-- 创建链接服务器
EXEC master.dbo.sp_addlinkedserver
  @server = N'DS',         -- 链接服务器名称（自定义）
  @srvproduct = N'DS',    --用户生产环境的实例名
  @provider = N'OraOLEDB.Oracle',   -- 推荐使用 Oracle Provider
  @datasrc = N'192.168.1.1/topprod';          -- 对应 tnsnames.ora 中的名称

-- 配置登录凭据
EXEC master.dbo.sp_addlinkedsrvlogin
  @rmtsrvname = N'DS',
  @useself = N'False',              -- 不使用当前 SQL 登录
  @locallogin = NULL,               -- 对所有本地登录生效
  @rmtuser = N'ds',                 -- Oracle 账户
  @rmtpassword = N'ds';             -- Oracle 密码

-- 启用 RPC 调用（可选）
EXEC sp_serveroption 'ds', 'rpc out', 'true';

--测试是否建立成功
select * from openquery(DS,'select 1 from sma_file')

如果需要删除建立的链接：

-- 删除链接服务器的登录映射（必须先于链接服务器删除）
EXEC sp_droplinkedsrvlogin 
  @rmtsrvname = 'DS',  -- 你的链接服务器名称
  @locallogin = NULL;           -- 删除所有关联登录

-- 删除链接服务器本身
EXEC sp_dropserver 
  @server = 'DS',      -- 你的链接服务器名称
  @droplogins = 'droplogins';   -- 确保删除所有残留登录