SQLSERVERZ之创建dblink

一、sqlserver

1.1sqlserver常见dblink

路径:数据库-》点击-》服务器对象-》链接服务器对象-》界面新增/或脚本新增(注意:界面新增,按照指示一步一步操作即可)

脚本命令如下:

USE [master]
GO

/****** Object:  LinkedServer [dblink名称]    Script Date: 2021-05-18 20:27:02 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'dblink名称', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'数据库地址,端口'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'dblink名称',@useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'dblink名称', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

1.2dblink使用(跨库使用dblink进行查询)

select * from dblink名称.his.dbo.表名where operdate=20210517

二、oracle创建dblink,实现跨库使用

1.1创建dblink

create database  link dblink名称 connect to 远程数据库用户名称identified by 远程数据库用户密码 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.101.5)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = search)))';

TestDblink : 表示dblink名字

dbName :表示 远程数据库的用户

dbPassword:表示 远程数据库的密码

HOST : 表示远程数据库IP

PORT : 表示远程数据库端口

SERVICE_NAME : 远程数据库的实例名

 

 1.2oracle的dblink使用(注意数据库名称和dblink的大小写 )

 

insert into 当前库 select xtzx.seq_eid.nextval,sysdate,sysdate, t.* from "CESHI_H_MATCH8888"@RZRQHIS8611 t;

 

上一篇:1008 Elevator


下一篇:3.pg客户端工具使用