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