SqlServer跨库查询

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

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',1 
reconfigure 
exec sp_configure 'Ad Hoc Distributed Queries',1 
reconfigure 
   

关闭Ad Hoc Distributed Queries： 
exec sp_configure 'Ad Hoc Distributed Queries',0 
reconfigure 
exec sp_configure 'show advanced options',0 
reconfigure

 

 

 

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