T-SQL——關於跨庫連線查詢

shanzm發表於2021-08-29
SQL

目錄

shanzm-2021年8月28日 16:56:34

0. 同一臺伺服器不同資料庫

同一個伺服器中在一個連線查詢不同的資料庫中的表，可以在表名前加上資料庫的名為字首

【示例】

select * from 資料庫.dbo.表

1. 使用跨庫查詢函式——OpenDataSource()

在不同伺服器中，一個伺服器的連線中查詢另外一個伺服器中的資料庫,可以在sql語句中使用OpenDataSource('sqloledb','server=伺服器IP地址;uid=資料庫登陸使用者;pwd=密碼').資料庫名.dbo.表名

【示例】

select * from OpenDataSource('sqloledb','server=xxx.xxx.xxx.xxx;uid=sa;pwd=*****').database_name.dbo.table_name

【說明】

  • OpenDataSource()函式不接受引數變數。只能是寫死指定的連線字串，不能使用變數，所以在儲存過程中不能動態的設定OpenDataSource()引數，這一點及其的不安全&不方便

  • 使用OpenDataSource()函式需要啟用一些高階配置選項：

    • 第一次使用的時候，可能會提示：
    訊息 15281，級別 16，狀態 1，第 1 行
SQL Server 阻止了對元件“Ad Hoc Distributed Queries”的 STATEMENT“OpenRowset/OpenDatasource”的訪問，因為此元件已作為此伺服器安全配置的一部分而被      關閉。系統管理員可以通過使用 sp_configure 啟用“Ad Hoc Distributed Queries”。有關啟用“Ad Hoc Distributed Queries”的詳細資訊，請搜尋 SQL Server       聯機叢書中的“Ad Hoc Distributed Queries”。

    可以在當前連線的資料庫中，通過以下語句開啟該元件

    exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

    關閉上述元件可以執行一下語句：

    exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure

2. 使用連結伺服器(Linking Server)

  1. 使用圖形介面建立連結伺服器：

    建立連結伺服器，可以使用MSSMS-->伺服器物件-->連結伺服器-->右鍵：新建連結伺服器

    常規：

    • 連結伺服器：伺服器IP地址
    • 伺服器型別：選擇SQL Server

    安全性：

    • 選中使用此安全上下文建立連線
    • 輸入使用者名稱和密碼

  2. 使用儲存過程建立連結伺服器

在當前伺服器的連線中，同時執行以下儲存過程，建立其他伺服器(即目標伺服器)為連結伺服器

exec sp_addlinkedserver--建立連結伺服器
@server='連結名稱',--要訪問的伺服器別名（一般就是直接使用伺服器地址）
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='目標伺服器地址'--要訪問的伺服器地址
go
exec sp_addlinkedsrvlogin--新增連結伺服器的登陸使用者
'連結名稱',--被訪問的伺服器別名
'false',
 null,
'使用者名稱',--目標伺服器賬號
'密碼'--目標伺服器密碼

示例：從連結伺服器中查詢

select * from 連結伺服器別名.database_name.dbo.table_name

3. 使用OpenDataSource()函式和連結伺服器的使用區別

  • OpenDataSource()功能相對連結伺服器少一些
  • 對於需要頻繁訪問的資料來源，要將其設定為連結伺服器，而不是使用OpenDataSource。
  • OpenDataSoucre()每次呼叫都明文提供連線字串，包含密碼，相對不安全

4. 參考

相關文章