[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:\Finance\account.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='S1\instance1'

B. 使用用於 Jet 的 Microsoft OLE DB 提供者
此樣本建立一台名為 SEATTLE Mktg 的連結的伺服器。

 

說明  本樣本假設已經安裝 Microsoft Access 和樣本 Northwind 資料庫，且 Northwind 資料庫駐留在 C:\Msoffice\Access\Samples。

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:\MSOffice\Access\Samples\Northwind.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:\MSOffice\Access\Samples\Northwind.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',
   @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:\MyData\DistExcl.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:\data\distqry',
   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 連結的伺服器。然後，該樣本對此連結的伺服器使用一個直接傳遞查詢。

說明  本樣本假定已經建立了一個名為 ORCLDB 的 Oracle 資料庫別名。

EXEC sp_addlinkedserver 'OracleSvr',
   'Oracle 7.3',
   'MSDAORA',
   '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:\MSOffice\Access\Samples\northwind.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:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders)
   AS o
   ON c.CustomerID = o.CustomerID
GO