本来想用sp_addlinkedserver但是可能是没权限吧,曲线救国,用EXTERNAL TABLE。
步骤:
1,CREATE MASTER KEY ENCRYPTION BY PASSWORD = '123456';
2,CREATE DATABASE SCOPED CREDENTIAL DBQueryAbcd
WITH IDENTITY = 'abcde',
SECRET = '123456';
此为远程服务器用户名密码
3,CREATE EXTERNAL DATA SOURCE DBQueryAbcdDataSrc WITH
(TYPE = RDBMS,
LOCATION = 'abcdefg.database.chinacloudapi.cn',
DATABASE_NAME = 'usertable',
CREDENTIAL = DBQueryAbcd,
) ;
此为远程DB地址与名字
4,CREATE EXTERNAL TABLE dbo.usertable
(
[id] [int] NOT NULL,
[usid] [nvarchar](256) NULL,
[user_name] [nvarchar](256) NULL
)
WITH
( DATA_SOURCE = DBQueryAbcdDataSrc)
此结构要与原表相同
结束