|
[SQL SERVER] 跨服务器查询
方法一:
用OPENDATASOURCE
下面是个跨SQLServer查询的示例
Select TableA.*,TableB.* From OPENDATASOURCE(
???????? 'SQLOLEDB',
???????? 'Data Source=ServerA;User ID=UserID;Password=Password'
???????? ).databaseAName.dbo.TableA
Left Join
?OPENDATASOURCE(
???????? 'SQLOLEDB',
???????? 'Data Source=ServerB;User ID=UserID;Password=Password'
???????? ).databaseBName.dbo.TableB On TableA.key=TableB.key
下面是个查询的示例，它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
? 'Data Source="c:Financeaccount.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
方法二(也可以在企业管理器里添加 linkServer):
sp_addlinkedserver
创建一个链接的服务器，使其允许对分布式的、针对 OLE DB 数据源的异类查询进行访问。在使用 sp_addlinkedserver 创建链接的服务器之后，此服务器就可以执行分布式查询。如果链接服务器定义为 Microsoft? SQL Server?，则可执行远程存储过程。
语法
sp_addlinkedserver [ @server = ] 'server'
??? [,[ @srvproduct = ] 'product_name' ]
??? [,[ @provider = ] 'provider_name' ]
??? [,[ @datasrc = ] 'data_source' ]
??? [,[ @location = ] 'location' ]
??? [,[ @provstr = ] 'provider_string' ]
??? [,[ @catalog = ] 'catalog' ]
权限
执行许可权限默认授予 sysadmin 和 setupadmin 固定服务器角色的成员。
简单示例:
//创建linkServer
exec sp_addlinkedserver 'srv_lnk','','SQLOLEDB','服务器名'
//登陆linkServer
exec sp_addlinkedsrvlogin 'srv_lnk','false',null,'用户名','密码'
//查询linkServer的数据库DataBaseA的表TableA
Select * From srv_lnk.DataBaseA.dbo.TableA
//List the tables in the linked server
EXEC sp_tables_ex txtsrv
?
示例
A. 使用用于 SQL Server 的 Microsoft OLE DB 提供程序
使用用于 SQL Server 的 OLE DB 创建链接服务器
下面的示例创建一台名为 SEATTLESales 的链接服务器，该服务器使用用于 SQL Server 的 Microsoft OLE DB 提供程序。
USE master
GO
EXEC sp_addlinkedserver
??? 'SEATTLESales',
??? N'SQL Server'
GO
在 SQL Server 的实例上创建链接服务器
此示例在 SQL Server 的实例上创建一台名为 S1_instance1 的链接服务器，该服务器使用 SQL Server 的 Microsoft OLE DB 提供程序。
EXEC??? sp_addlinkedserver??? @server='S1_instance1',@srvproduct='',
??????????????????????????????? @provider='SQLOLEDB',@datasrc='S1instance1'
B. 使用用于 Jet 的 Microsoft OLE DB 提供程序
此示例创建一台名为 SEATTLE Mktg 的链接服务器。
?
说明? 本示例假设已经安装 Microsoft Access 和示例 Northwind 数据库，且 Northwind 数据库驻留在 C:MsofficeAccessSamples。
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
?? @server = 'SEATTLE Mktg',
?? @provider = 'Microsoft.Jet.OLEDB.4.0',
?? @srvproduct = 'OLE DB Provider for Jet',
?? @datasrc = 'C:MSOfficeAccessSamplesNorthwind.mdb'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
?? 'SEATTLE Mktg',
?? 'OLE DB Provider for Jet',
?? 'Microsoft.Jet.OLEDB.4.0',
?? 'C:MSOfficeAccessSamplesNorthwind.mdb'
GO
C. 使用用于 Oracle 的 Microsoft OLE DB 提供程序
此示例创建一台名为 LONDON Mktg 的链接服务器，该服务器使用用于 Oracle 的 Microsoft OLE DB 提供程序，并且假设此 Oracle 数据库的 SQL*Net 别名为 MyServer。
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
?? @server = 'LONDON Mktg',
?? @srvproduct = 'Oracle',
?? @provider = 'MSDAORA',
?? @datasrc = 'MyServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
?? 'LONDON Mktg',
?? 'Oracle',
?? 'MSDAORA',
?? 'MyServer'
GO
D. 将 data_source 参数与用于 ODBC 的 Microsoft OLE DB 提供程序一起使用
此示例创建一台名为 SEATTLE Payroll 的链接服务器，该服务器使用用于 ODBC 的 Microsoft OLE DB 提供程序和 data_source 参数。
?
说明? 在执行 sp_addlinkedserver 之前，必须在服务器上将指定的 ODBC 数据源名称定义为系统 DSN。
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
?? @server = 'SEATTLE Payroll',
?? @provider = 'MSDASQL',
?? @datasrc = 'LocalServer'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
?? 'SEATTLE Payroll',
?? '',
?? 'MSDASQL',
?? 'LocalServer'
GO
E. 将 provider_string 参数与用于 ODBC 的 Microsoft OLE DB 提供程序一起使用
此示例创建一台名为 LONDON Payroll 的链接服务器，该服务器使用用于 ODBC 的 Microsoft OLE DB 提供程序和 provider_string 参数。
?
说明? 有关 ODBC 连接字符串的更多信息，请参见 SQLDriverConnect 和如何分配句柄并与 SQL Server (ODBC) 连接。
USE master
GO
-- To use named parameters:
EXEC sp_addlinkedserver
?? @server = 'LONDON Payroll',
?? @provstr = 'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
GO
-- OR to use no named parameters:
USE master
GO
EXEC sp_addlinkedserver
?? 'LONDON Payroll',
?? 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',
?? 'c:MyDataDistExcl.xls',
?? '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',
?? 'c:datadistqry',
?? '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 链接的服务器。然后，该示例对此链接的服务器使用一个直接传递查询。
说明? 本示例假定已经创建了一个名为 ORCLDB 的 Oracle 数据库别名。
EXEC sp_addlinkedserver 'OracleSvr',
?? 'Oracle 7.3',
?? 'ORCLDB'
GO
SELECT *
FROM OPENQUERY(OracleSvr,'SELECT name,id FROM joe.titles')
GO
方法四:
OPENROWSET
包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。当访问链接服务器中的表时，这种方法是一种替代方法，并且是一种使用 OLE DB 连接并访问远程数据的一次性的、特殊的方法。可以在查询的 FROM 子句中像引用表名那样引用 OPENROWSET 函数。依据 OLE DB 提供程序的能力，还可以将 OPENROWSET 函数引用为 INSERT、UPDATE 或 DELETE 语句的目标表。尽管查询可能返回多个结果集，然而 OPENROWSET 只返回第一个。
语法
OPENROWSET ( 'provider_name'
???,{ 'datasource' ; 'user_id' ; 'password'
??????? | 'provider_string' }
???,{ [ catalog.] [ schema.] object
??????? | 'query' }
??? )
示例
A. 将 OPENROWSET 与 SELECT 语句及用于 SQL Server 的 Microsoft OLE DB 提供程序一起使用
下面的示例使用用于 SQL Server 的 Microsoft OLE DB 提供程序访问 pubs 数据库中的 authors 表，该数据库在一个名为 seattle1 的远程服务器上。从 datasource、user_id 及 password 中初始化提供程序，并且使用 SELECT 语句定义返回的行集。
USE pubs
GO
SELECT a.*
FROM OPENROWSET('SQLOLEDB','seattle1';'sa';'MyPass',
?? 'SELECT * FROM pubs.dbo.authors ORDER BY au_lname,au_fname') AS a
GO
B. 将 OPENROWSET 与对象及用于 ODBC 的 OLE DB 提供程序一起使用
下面的示例使用用于 ODBC 的 OLE DB 提供程序以及 SQL Server ODBC 驱动程序访问 pubs 数据库中的 authors 表，该数据库在一个名为 seattle1 的远程服务器中。提供程序用在 ODBC 提供程序所用的 ODBC 语法中指定的 provider_string 进行初始化，定义返回的行集时使用 catalog.schema.object 语法。
USE pubs
GO
SELECT a.*
FROM OPENROWSET('MSDASQL',
?? 'DRIVER={SQL Server};SERVER=seattle1;UID=sa;PWD=MyPass',
?? pubs.dbo.authors) AS a
ORDER BY a.au_lname,a.au_fname
GO
C. 使用用于 Jet 的 Microsoft OLE DB 提供程序
下面的示例通过用于 Jet 的 Microsoft OLE DB 提供程序访问 Microsoft Access Northwind 数据库中的 orders 表。
?
说明? 下面的示例假定已经安装了 Access。
USE pubs
GO
SELECT a.*
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
?? 'c:MSOfficeAccessSamplesnorthwind.mdb';'admin';'mypwd',Orders)
?? AS a
GO
D. 使用 OPENROWSET 和 INNER JOIN 中的另一个表
下面的示例从本地 SQL Server Northwind 数据库的 customers 表中，以及存储在相同计算机上 Access Northwind 数据库的 orders 表中选择所有数据
?
说明? 下面的示例假定已经安装了 Access。
USE pubs GO SELECT c.*,o.* FROM Northwind.dbo.Customers AS c INNER JOIN ?? OPENROWSET('Microsoft.Jet.OLEDB.4.0',?? 'c:MSOfficeAccessSamplesnorthwind.mdb';'admin';'mypwd',Orders) ?? AS o ?? ON c.CustomerID = o.CustomerID GO
（编辑：李大同）
【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!
|
