分布式查询

/*分布式查询*/
分布式查询(Distributed Query)能够访问存放在同一部计算机或不同计算机上的SQL Server或不同种类的数据源。众所周知，OLE DB是Microsoft规格的公用数据访问的应用程序开发界面(Application Programming Interface,API)，而SQL Server2000即是使用OLE DB来支持分布式查询的。
SQL Server2000中提供了两种方法来实现访问不同种类的OLE DB数据源：第一，使用“链接服务器”(Linked Server)；第二，使用“特定名称”(Ad Hoc Names)。下面，我们将就这两种方法进行详细的阐述。
(一) 链接服务器
链接服务器是一个定义于SQL Server2000中的虚拟服务器，链接服务器的名称是使用系统存储过程sp_addlinkedserver定义的。
当要处理链接服务器中的对象时，必须使用完整的四部分名称：linked_server_name.catalog.schema.object_name。

说明：
这四部分名称对应于SQL Server而言，分别是：引用OLE DB数据源的链接服务器名称、数据库名称、所有者、数据表或视图。

注意：SQL Server不支持跨链接服务器的全文检索搜索！

基本上，SQL Server允许您采用如下方式来访问链接服务器中的数据：
a、分布式查询。可以在SELECT、INSERT、UPDATE与DELETE等T-SQL命令语句中以四部分名称来访问链接服务器中的数据表或视图。
b、使用OPENQUERY()函数在链接服务器上运行查询。该函数会将一条命令传送给链接服务器来运行，它返回的行集可以在T-SQL命令
语句中以一个数据表或视图的形式来使用。
c、远程存储过程。可以使用四部分名称来运行链接服务器中的存储过程。

特别提醒：我们应该将一个经常需要查询的OLE DB数据源定义成一个链接服务器，然而，一个查询频率很低的OLE DB数据源将不值得特别为它定义一个链接服务器，此时应该采用特定名称的方式。

(二) 使用sp_addlinkedserver创建链接服务器
sp_addlinkedserver的语法如下所示：
sp_addlinkedserver [@server=] 'server'
[,[@srvproduct=] 'product_name']
[,[@provider=] 'provider_name']
[,[@datasrc=] 'data_source']
[,[@location=] 'location']
[,[@provstr=] 'provider_string']
[,[@catalog=] 'catalog']
各具体参数说明如下：
(1) [@server] 'server'
是指所要创建的链接服务器的本地名称。此名称不能与已存在的链接服务器的名称相同。同时请注意：如果您并未使用[@datasrc=] 'data_source'参数来赋值OLE DB数据源名称，则[@server] 'server'必须是OLE DB数据源的名称。
(2) [@srvproduct=] 'product_name'
是指OLE DB数据源的产品名称。此参数的数据类型是nvarchar(128)，默认值是NULL。
如果OLE DB数据源就是SQL Server，那么可以使用N'SQL Server'作为设置值，而且此时不再需要赋值provider_name、data_source、location、provider_string与catalog，不过采用这个方法，[@server] 'server'必须
是SQL Server的网络名称(即所在计算机的名称)。
如果OLE DB数据源是SQL Server之外的数据源，则您可以自定义产品名称。
说明：如果您所创建的链接服务器也是一个SQL Server，则可以运行此链接服务器上的存储过程，即运行
所谓的远程存储过程。
(3) [@provider=] 'provider_name'
是指OLE DBProvider的PROGID。
安装于计算机中的每一个OLE DB Provider都会拥有一个惟一的provider_name。此参数的数据类型是
nvarchar(128)，默认值为NULL。
下面列出了各种OLE DB数据源对应的provider_name：
远程OLE DB数据源        OLE DB Provider                                Provider_name
SQL Server             Microsoft OLE DB Provider for SQL Server       SQLOLEDB
Access/Jet             Microsoft OLE DB Provider for Jet              Microsoft.Jet.OLEDB.4.0
Microsoft Excel工作簿   Microsoft OLE DB Provider for Jet              Micorsoft.Jet.OLEDB.4.0
文本文件                Microsoft OLE DB Provider for Jet              Micorsoft.Jet.OLEDB.4.0
ORACLE                 Microsoft OLE DB Provider for Oracle           MSDAORA
IBM DB2数据库          Microsoft OLE DB Provider for DB2               DB2OLEDB
ODBC数据源             Microsoft OLE DB Provider for ODBC              MSDASQL
文件系统               Microsoft OLE DB Provider for Indexing Service  MSIDXS
(4) [@datasrc=] 'data_source'
是OLE DB Provider所解释的数据源名称。此参数的数据类型是nvarchar(128)，默认值为NULL。对不同的
OLE DB Provider而言，数据源所代表的对象与赋值方式也有所不同，请详见下表所示：
远程OLE DB数据源      OLE DB Provider                                   data_source
SQL Server            Microsoft OLE DB Provider for SQL Server          所在计算机名称
Access/Jet            Microsoft OLE DB Provider for Jet                 数据库文件.mdb的完整路径与文件名称
Microsoft Excel工作簿   Microsoft OLE DB Provider for Jet               工作簿.xls的完整路径与文件名称
文本文件                Microsoft OLE DB Provider for Jet               文本文件.txt的完整路径但不含文件名称
ORACLE                 Microsoft OLE DB Provider for Oracle             指向某个Oracle数据库的SQL*Net别名
IBM DB2数据库          Microsoft OLE DB Provider for DB2                 不需指定，它是通过provider_string来赋值的
ODBC数据源             Microsoft OLE DB Provider for ODBC                如果不采用连接字符串，则为ODBC数据源的系统DSN，否则不需赋值数据源
文件系统               Microsoft OLE DB Provider for Indexing Service    可以运行属性搜索或全文检索的内容文件
(5) [@location=] 'location'
是指由OLE DB Provider所解释的数据库位置。此参数的数据类型是nvarchar(4000)，默认值为NULL。
(6) [@provstr=] 'provider_string'
是一个能够识别惟一数据源的OLE DB Provider连接字符串。此参数的数据类型是nvarchar(128)，默认值为NULL。
并不是所有的OLE DB数据源都需要赋值连接字符串，只有下表中列出的才需要：
远程OLE DB数据源      OLE DB Provider                                provider_string
Microsoft Excel工作簿   Microsoft OLE DB Provider for Jet            Excel 5.0
文本文件                Microsoft OLE DB Provider for Jet            Text
IBM DB2数据库          Microsoft OLE DB Provider for DB2             连接字符串
ODBC数据源             Microsoft OLE DB Provider for ODBC            如果不赋值数据源，就必须使用ODBC连接字符串
(7) [@catalog=] 'catalog'
是指当前连接到OLE DBProvider时所使用的目录。对SQL Server而言，目录就是指数据库。

以下，我们将通过数个范例来实际展示如何使用系统存储过程sp_addlinkedserver创建链接服务器，以及如何通过分布式查询去
查询链接服务器中的数据表。
/*范例1*/
use master
go
exec sp_addlinkedserver
 @server='tpserver',
 @srvproduct=N'SQL Server'
go
/*
说明：在本例中，我们直接使用语法中的[@srvproduct=] 'product_name'将产品名称设置为N'SQL Server'，因此所赋值的链接服务器的本地名称tpserver必须就是所要链接的SQL Server的网络名称。当然，您也可以将tpserver更改成您网络上的某一个SQL Server的名称。
*/
--查询创建于本地计算机上的各个链接服务器
exec sp_linkedservers
go
--删除链接服务器
exec sp_dropserver 'tpserver'

/*范例2*/
use northwind
go
--如果并未赋值数据源，则服务器名称必须就使所要链接的服务器名称
exec sp_addlinkedserver @server='tpserver',
   @srvproduct=N'SQL Server'
go

--自定义链接服务器的名称，此时必须赋值其他参数
--链接服务器MyLinkedServer1的数据源是网络名称为tpserver的SQL Server
exec sp_addlinkedserver @server='mylinkedserver1',
   @srvproduct=N'',
   @provider=N'SQLOLEDB',
   @datasrc=N'tpserver'
go

--自定义链接服务器的名称并赋值当前数据库
--链接服务器MyLinkedServer2的数据源是网络名称为tpserver1的SQL Server
exec sp_addlinkedserver @server='mylinkedserver2',
   @srvproduct=N'',
   @provider=N'SQLOLEDB',
   @datasrc=N'tpserver1',
   @catalog=N'northwind'
go

--以四部分名称查询链接服务器MyLinkedServer1上的dbo.Orders数据表
select * from mylinkedserver1.northwind.dbo.Orders

--以四部分名称查询链接服务器MyLinkedServer1上的各个关联数据表
select a.OrderID,a.OrderDate,b.UnitPrice,c.ProductName
from mylinkedserver1.northwind.dbo.Orders a
 inner join mylinkedserver1.northwind.dbo.Order Details b
  on a.OrderID=b.OrderID
 inner join mylinkedserver1.northwind.dbo.Products c
  on b.ProductID=c.ProductID
go

--将查询命令传送到链接服务器tpserver上运行
select * from openquery(tpserver,'select * from northwind.dbo.Employees')
go

/*范例3*/
/*使用Microsoft OLE DB Provider for Jet为一个Access数据库“北风.mdb”创建一个名称为“北风”的链接服务器*/
use master
go
exec sp_addlinkedserver
 @server='北风',
 @srvproduct=N'Access 2002',
 @provider=N'Microsoft.Jet.OLEDB.4.0',
 @datasrc=N'd:/T-SQL/Database/北风.mdb'
go

--采用SQL Server的帐户验证连接到SQL Server，则以用户Admin且没有密码登录链接服务器“北风”
exec sp_addlinkedsrvlogin '北风','false',NULL,'Admin',NULL
go
--由于Access中没有Catalog(数据库)与schema(所有者)名称，因此在分布式查询中请使用linked_server...table_name的四部分名称形式
select * from 北风...orders
go

/*范例4*/
/*使用Microsoft OLE DB Provider for Jet为一个Excel工作簿“订单.xls”创建一个名称为“表格”的链接服务器*/
提示：可以将工作簿中的每个工作表(sheet)作为一个数据表来使用，而工作表的名称就好比像数据表的名称。由于Excel中没有Catalog(数据库)与schema(所有者)名称，因此在分布式查询中请使用linked_server...table_name的四部分名称形式。
use master
go
exec sp_addlinkedserver
 @server='表格',
 @srvproduct=N'Excel 2002',
 @provider=N'Microsoft.Jet.OLEDB.4.0',
 @datasrc=N'd:/T-SQL/Database/订单.xls',
 @location=NULL,
 @provstr=N'Excel 5.0'  --提供程序字符串是'Excel 5.0'
go
--采用SQL Server的帐户验证连接到SQL Server，则以用户Admin且没有密码登录链接服务器“表格”
exec sp_addlinkedsrvlogin '表格','false',NULL,'Admin',NULL
go
--运行分布式查询
select * from 表格...发票
select * from 表格...产品订货明细

/*范例5*/
/*使用Microsoft OLE DB Provider for Jet为d:/T-SQL/Database目录中的所有文件创建一个名称为“文本服务器”的链接服务器*/
为文本文件创建链接服务器时，请注意以下事项：
a、在sp_addlinkedserver语句中的@datasrc参数中，只需要赋值文本文件的存放路径而不需要赋值文件名称。事实上，所赋值路径下的每一个文本文件都会被当作一个数据表。可以使用系统存储过程sp_tables_ex加以查验。
b、文本文件所在目录必须存在一个结构描述文件schema.ini中。schema.ini用来描述各个文本文件的以便您在运行分布式查询时能够正确访问各字段的数据。事实上，当您使用Access将数据表或查询导出到一个文本文件时，即会自动为您创建一个shcema.ini。
c、没有Catalog(数据库)与schema(所有者)名称，因此在分布式查询中请使用linked_server...[文本文件名称#txt]的四部分名称形式
use master
go
exec sp_addlinkedserver
 @server='文本服务器',
 @srvproduct=N'Text file',
 @provider=N'Microsoft.Jet.OLEDB.4.0',
 @datasrc=N'd:/T-SQL/Database',
 @location=NULL,
 @provstr=N'Text'  --提供程序字符串是'Text'
go
--列出链接服务器中的数据表，该目录中的每个文本文件都可以被当作一个数据表来查询
exec sp_tables_ex 文本服务器
go
--采用SQL Server的帐户验证连接到SQL Server，则以用户Admin且没有密码登录链接服务器“文本服务器”
exec sp_addlinkedsrvlogin '表格','false',NULL,'Admin',NULL
go
--运行分布式查询
select * from 文本服务器...[每一位客户的订货总金额#txt]
select * from 文本服务器...[各年度每月订货小计#txt]

(三) 使用openquery()函数
openquery()函数能够在linked_server参数所赋值名称的链接服务器上运行'query'参数所赋值的查询字符串，并将结果集返回。
openquery()函数不仅可以使用在SELECT命令的FROM参数中，而且根据OLE DB Provider的功能openquery()函数甚至能够作为
INSERT、UPDATE或DELETE命令的目标数据表。虽然'query'参数所赋值的查询字符串可能返回多个结果集，但是openquery()函数
只会返回第一个结果集。
/*范例1*/
use master
go
--链接服务器AccessXP的数据源是一个Access数据库
exec sp_addlinkedserver
 @server='AccessXP',
 @srvproduct=N'Access 2002',
 @provider=N'Microsoft.Jet.OLEDB.4.0',
 @datasrc=N'D:/T-SQL/Database/北风.mdb'
go
--采用SQL Server的帐户验证连接到SQL Server，则以用户Admin且没有密码登录链接服务器AccessXP
exec sp_addlinkedsrvlogin 'AccessXP','false',NULL,'Admin',NULL
go
--链接服务器mylinkedserver1的数据源是网络名称为tpserver1的SQL Server，并赋值当前数据库为northwind
exec sp_addlinkedserver
 @server='mylinkedserver1',
 @srvproduct=N'',
 @provider=N'SQLOLEDB',
 @datasrc=N'tpserver1',
 @catalog=N'Northwind'
go
--链接服务器mylinkedserver2的数据源是网络名称为tpserver2的SQL Server，并赋值当前数据库为northwind
exec sp_addlinkedserver
 @server='mylinkedserver2',
 @srvproduct=N'',
 @provider=N'SQLOLEDB',
 @datasrc=N'tpserver2',
 @catalog=N'Northwind'
go
use northwind
go
--将openquery()函数返回从各个远程不同种类的数据源所返回的数据加以合并然后存入一个新数据表中
select * into 新数据表
from openquery(AccessXP,'select 姓名,Datediff("yyyy",出生日期,Date()) as 年龄,地址
  from tbname where 地址 like ''%北京市%''')
union all
select * from openquery(mylinkedserver1,'select 姓名,年龄=datediff(yy,出生日期,getdate()),地址
  from tbname1 where 地址 like ''%深圳市%''')
union all
select * from openquery(mylinkedserver2,'select 姓名,年龄=datediff(yy,出生日期,getdate()),地址
  from tbname2 where 地址 like ''%天津市%''')
order by 2 desc
go
--最后来查询我产生的新数据表
select * from 新数据表
go

--将openquery()函数返回从各个远程不同种类的数据源所返回的数据加以链接以便获得相关联的数据
select a.客户编号,a.公司名称,year(b.订单日期) as 年份,month(b.订单日期) as 月份,
 sum(c.单价*c.数量*(1-c.折扣)) as 小计
from openquery(AccessXP,'select * from 客户') a
 inner join
     openquery(mylinkedserver1,'select * from 订单') b
 on a.客户编号=b.客户编号
 inner join
     openquery(mylinkedserver2,'select * from 订单明细') c
 on b.订单编号=c.订单编号
group by a.客户编号,a.公司名称,year(b.订单日期),month(b.订单日期)
order by a.公司名称,year(b.订单日期),month(b.订单日期)
go

/*范例2*/
/*如果链接服务器的数据源是SQL Server，而且此SQL Server拥有会返回结果集的存储过程，您可以通过OPENQUERY()函数去运行该链接服务器上的存储过程*/
use master
go
--链接服务器mylinkedserver1的数据源是网络名称为tpserver1的SQL Server，并赋值当前数据库为northwind
exec sp_addlinkedserver
 @server='mylinkedserver1',
 @srvproduct=N'',
 @provider=N'SQLOLEDB',
 @datasrc=N'tpserver1',
 @catalog=N'Northwind'
go
--链接服务器mylinkedserver2的数据源是网络名称为tpserver2的SQL Server，并赋值当前数据库为northwind
exec sp_addlinkedserver
 @server='mylinkedserver2',
 @srvproduct=N'',
 @provider=N'SQLOLEDB',
 @datasrc=N'tpserver2',
 @catalog=N'Northwind'
go

--将EXEC命令传送到链接服务器mylinkedserver1上运行以便运行存储过程，由于mylinkedserver1的定义已赋值默认数据库，
--因此在EXEC命令中不需要赋值存储过程所属的数据库
select * from openquery(mylinkedserver1,'exec proc1')
--将EXEC命令传送到链接服务器mylinkedserver2上运行以便运行存储过程，由于mylinkedserver2的定义每月赋值默认数据库，
--因此中EXEC命令中需要赋值存储过程所属的数据库
select * from openquery(mylinkedserver1,'exec northwind.dbo.proc1 ''%上海市%''')
go