T-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)
-
使用圖形介面創建鏈接伺服器：
創建鏈接伺服器，可以使用MSSMS–>伺服器對象–>鏈接伺服器–>右鍵：新建鏈接伺服器
常規：
- 鏈接伺服器：伺服器IP地址
- 伺服器類型：選擇SQL Server
安全性：
- 選中使用此安全上下文建立連接
- 輸入用戶名和密碼
-
使用存儲過程創建鏈接伺服器
在當前伺服器的連接中，同時執行以下存儲過程，創建其他伺服器(即目標伺服器)為鏈接伺服器
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()每次調用都明文提供連接字元串，包含密碼，相對不安全
