SqlServer跨库查询

时间：2023-03-08 16:34:27

由于业务的拆分，数据库拆分为两种作用：

  1. 汇总数据库（Master，头节点数据库），
  2. 子节点数据库（Compute Node,计算子节点数据库）

这样，就设计到子节点访问头节点数据库中的某张汇总表，这种表的记录一般在几，到几十万行左右，目前适合做跨库查询。

跨库查询目前分为两种：

  1. 通过sp_addlinkedserver建立链接服务器
  2. 没有链接服务器时，可以使用openrowset或者opendatasource函数
    1. 在部署时，需要在SQLSERVER外围应用配置器中启用OpenRowSet和OpenDataSource支持
    2. 较第一种（通过sp_addlinkedserver建立链接服务器）简单，适合简单的sql语句中，如果是复杂的逻辑，需要有语句块、函数或者存储过程才能完成时，建议使用第一种（即时创建、销毁连接服务器）
  • 通过sp_addlinkedserver建立链接服务器
    • 使用步骤
      • 通过Sp_AddLinkedServer创建链接服务器
        • 示例： Exec Sp_AddLinkedServer 'RemoteLinkMasterDB','','SqlOleDB','172.21.1.1\MyDBInstace'
      • 通过Sp_AddLinkedSrvLogin 创建链接服务器使用账户、密码
        • 示例： Exec Sp_AddLinkedSrvLogin 'RemoteLinkMasterDB','false',null,'MyMasterDataBaseUser','MyMasterDBBaseUserPassword'
      • 验证连接服务器是否存在
        • 代码示例：if exists(select 1 from master..sysservers where srvname='RemoteLinkMasterDB'
      • 通过Sp_ServerOption 设置 RPC、RPC Out为True（如果不需要执行跨域存储过程则不需要设置此项）
        • Exec Sp_ServerOption 'RemoteLinkMasterDB','RPC','true';
        • Exec sp_ServerOption 'RemoteLinkMasterDB','RPC Out','true';
      • 使用之后，通过Sp_DropServer删除链接服务器、删除链接登陆
        • Exec Sp_DropServer 'RemoteLinkMasterDB','DropLogins';
      • SQL中使用示例：
        • 查询：Select * From RemoteLinkMasterDB.数据库名称(Database Name).Scheme(数据库架构).表/视图名；
        • 执行跨域存储过程：Exec RemoteLinkMasterDB.数据库名称(Database Name).Scheme(数据库架构).存储过程名（参数相关）；
        • 还可以使用openquery函数：Select * From OpenQuery(RemoteLinkMasterDB,'Select * From 数据库名称.架构名.表名')；
  • 没有链接服务器时，可以使用openrowset或者opendatasource函数
    • OpenRowSet用法示例：
      • Select * From OpenRowSet('SqlOleDB','172.21.1.1\MyDBInstace','MyMasterDataBaseUser','MyMasterDBBaseUserPassword','Select GetDate()');
      • Select * From OpenRowSet('SqlOleDB','Source=172.21.1.1\MyDBInstace;UID=MyMasterDataBaseUser;PWD=MyMasterDBBaseUserPassword','Select GetDate()');
    • OpenDataSource用法示例：
      • Select * From OpenDataSource('SqlOleDB','Data Source=172.21.1.1\MyDBInstace;User ID=MyMasterDataBaseUser;Password=MyMasterDBBaseUserPassword').数据库名.架构名.表名
    • 这两种方式必须开通 Ad Hoc Distributed Queries。
启用Ad Hoc Distributed Queries：

exec sp_configure 'show advanced options',

reconfigure

exec sp_configure 'Ad Hoc Distributed Queries',

reconfigure 

关闭Ad Hoc Distributed Queries：

exec sp_configure 'Ad Hoc Distributed Queries',

reconfigure

exec sp_configure 'show advanced options',

reconfigure

参考：http://www.2cto.com/database/201206/136383.html

相关文章