深入SQL SERVER 跨服务器查询详解

发布时间:2020-12-20编辑:脚本学堂
本文介绍下,有关sql server中跨服务器查询的相关内容,共介绍四种实现方法,并提供多个sql查询实例供参考,希望对大家有所帮助。

说明 有关 ODBC 连接字符串的更多信息,请参见 SQLDriverConnect 和如何分配句柄并与 SQL Server (ODBC) 连接。
 

复制代码 代码示例:
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
   @server = 'LONDON Payroll',
   @provider = 'MSDASQL',
   @provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
   'LONDON Payroll',
   '',
   'MSDASQL',
   NULL,
   NULL,
   'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO

F. 在 Excel 电子表格上使用用于 Jet 的 Microsoft OLE DB 提供程序
若要创建使用用于 Jet 的 Microsoft OLE DB 提供程序以访问 Excel 电子表格的链接服务器定义,请首先在 Excel 中创建一个命名的范围以指定要在 Excel 工作表中选择的行和列。然后,可将此范围的名称引用为分布式查询中的表名称。
 

复制代码 代码示例:
EXEC sp_addlinkedserver 'ExcelSource',
   'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:MyDataDistExcl.xls',
   NULL,
   'Excel 5.0'
GO

为了访问 Excel 电子表格中的数据,请将某个范围内的单元与某个名称相关联。通过将范围的名称用作表名称,可以访问指定的已命名范围。下列查询利用前面设置的链接服务器,可访问称为 SalesData 的命名范围。
 

复制代码 代码示例:
SELECT *
FROM EXCEL...SalesData
GO

G. 使用用于检索服务的 Microsoft OLE DB 提供程序
此示例创建一台链接服务器,并且使用 OPENQUERY 从为检索服务启用的链接服务器和文件系统中检索信息。
 

复制代码 代码示例:
EXEC sp_addlinkedserver FileSystem,
   'Index Server',
   'MSIDXS',
   'Web'
GO
USE pubs
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'yEmployees')
   DROP TABLE yEmployees
GO
CREATE TABLE yEmployees
(
id       int         NOT NULL,
lname    varchar(30) NOT NULL,
fname    varchar(30) NOT NULL,
salary   money,
hiredate datetime
)
GO
INSERT yEmployees VALUES
(
10,
'Fuller',
'Andrew',
$60000,
'9/12/98'
)
GO
IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS
      WHERE TABLE_NAME = 'DistribFiles')
   DROP VIEW DistribFiles
GO
CREATE VIEW DistribFiles
AS
SELECT *
FROM OPENQUERY(FileSystem,
                 'SELECT Directory,
                    FileName,
                    DocAuthor,
                    Size,
                    Create,
                    Write
                  FROM SCOPE('' "c:My Documents" '')
                  WHERE CONTAINS(''Distributed'') > 0
                    AND FileName LIKE ''%.doc%'' ')
WHERE DATEPART(yy, Write) = 1998
GO
SELECT *
FROM DistribFiles
GO
SELECT Directory,
FileName,
DocAuthor,
hiredate
FROM DistribFiles D, yEmployees E
WHERE D.DocAuthor = E.FName + ' ' + E.LName
GO

H. 使用用于 Jet 的 Microsoft OLE DB 提供程序访问文本文件
此示例创建一台直接访问文本文件的链接服务器,而没有将这些文件链接为 Access .mdb 文件中的表。提供程序是 Microsoft.Jet.OLEDB.4.0,提供程序字符串为"Text"。

数据源是包含文本文件的目录的完整路径名。schema.ini 文件(描述文本文件的结构)必须与此文本文件存在于相同的目录中。有关创建 schema.ini 文件的更多信息,请参见 Jet 数据库引擎文档。
 

复制代码 代码示例:

--Create a linked server
EXEC sp_addlinkedserver txtsrv, 'Jet 4.0',
   'Microsoft.Jet.OLEDB.4.0',
   'c:datadistqry',
   NULL,
   'Text'
GO

--Set up login mappings
EXEC sp_addlinkedsrvlogin txtsrv, FALSE, Admin, NULL
GO

--List the tables in the linked server
EXEC sp_tables_ex txtsrv
GO

--Query one of the tables: file1#txt
--using a 4-part name
SELECT *
FROM txtsrv...[file1#txt]

I. 使用用于 DB2 的 Microsoft OLE DB 提供程序
下面的示例创建一台名为 DB2 的链接服务器,该服务器使用用于 DB2 的 Microsoft OLE DB 提供程序。
 

复制代码 代码示例:
EXEC sp_addlinkedserver
   @server='DB2',
   @srvproduct='Microsoft OLE DB Provider for DB2',
   @catalog='DB2',
   @provider='DB2OLEDB',
   @provstr='Initial Catalog=PUBS;Data Source=DB2;HostCCSID=1252;Network Address=XYZ;Network Port=50000;Package Collection=admin;Default Schema=admin;'

方法三:
OPENQUERY
尽管查询可能返回多个结果集,但是 OPENQUERY 只返回第一个。

语法
OPENQUERY ( linked_server , 'query' )

参数
linked_server

一个标识符,表示链接的服务器的名称。

'query'

在链接的服务器中执行的查询字符串。

注释
OPENQUERY 不接受参数变量。

示例
下面的示例利用用于 Oracle 的 Microsoft OLE DB 提供程序针对 Oracle 数据库创建一个名为 OracleSvr 链接的服务器。然后,该示例对此链接的服务器使用一个直接传递查询。