您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> SQL Server 2005

SQL Server 2005 建立 Oracle 10g 連結伺服器實現異構資料同步完整教學

編輯：SQL Server 2005

1. 建立連結伺服器

在 SSMS 物件總管中展開【伺服器對象】→【連結伺服器】，右鍵選【新增連結伺服器】。

新增連結伺服器入口

新增連結伺服器對話框

2. 關鍵參數配置

一般頁籤

連結伺服器名稱：自訂（如 ORADBCONN）。
提供者：選 Microsoft OLE DB Provider for Oracle。
產品名稱、資料來源、提供者字串：填寫本地 Oracle Client tnsnames.ora 中配置的 Net 服務名（見圖）。

一般頁籤配置

資料來源範例

安全性頁籤

選「使用此安全性內容建立連線」，輸入 Oracle 遠程登入用戶名/密碼。

安全性頁籤

遠程登入憑證

完成建立

3. 異構查詢語法格式

四部分命名：[連結伺服器]..[Oracle用戶].[表名]，Oracle 部分必須大寫，否則報錯 7314。

-- 清空 Oracle 表
DELETE FROM ORADBCONN..HRSOFT.WEBUSER;

-- 同步 SQL Server 資料到 Oracle
INSERT INTO ORADBCONN..HRSOFT.WEBUSER
SELECT * FROM WEBUSER;

-- 查詢驗證
SELECT * FROM ORADBCONN..HRSOFT.WEBUSER;

4. 進階查詢技巧與效能優化

A. 直接四部分命名（易報型別不匹配、較慢）

SELECT * FROM [LINK2ORACLE]..[ORACLE_USER_NAME].TABLE_NAME;
-- 常見錯誤：OLE DB 提供者 "MSDAORA" 回傳的中繼資料不一致

B. `OPENQUERY` 推薦（穩定、效能近原生）

SELECT * FROM OPENQUERY(LINK2ORACLE, 'SELECT * FROM OracleUserName.TableName');
-- 可視為資料表使用，支援 JOIN、WHERE 等

C. `SELECT INTO` 快速建立本地表

SELECT * INTO OS_GIS_WEBUSER
FROM OPENQUERY(LINK2ORACLE, 'SELECT * FROM HRSOFT.WEBUSER');

D. 建立同義詞簡化程式碼

CREATE SYNONYM OS_GIS_CELL FOR [ORACLELK]..[CMCC].OS_GIS_CELL;
SELECT * FROM os_gis_cell;
SELECT * FROM os_gis_cell a WHERE a.CellName IS NULL;

5. 實戰範例：排程作業同步複雜報表資料

以下為實際生產環境作業腳本片段，展示透過 OPENQUERY 執行 Oracle 複雜查詢（含分析函數 DENSE_RANK、多表聯接），再寫入 SQL Server 本地表：

DELETE FROM XBLD_Information;
INSERT INTO XBLD_Information(job_name, num_layers, JB_PP1, JB_PC1, ..., BM)
SELECT * FROM OPENQUERY(INPLAN, '
    SELECT mjs15.job_name, mjs15.num_layers,
           mjs1.MM1 JB_PP1, mjs1.MC1 JB_PC1, ...,
           SUBSTR(mjs15.job_name,5,2) BM
    FROM ( ... ) mjs15
    LEFT JOIN ( ... ) mjs1 ON mjs15.job_name = mjs1.job_name
    LEFT JOIN ( ... ) mjs2 ON mjs15.job_name = mjs2.job_name
    ...
');
UPDATE XBLD_Information SET BM = b.Describe
FROM XBLD_Information a, XBLD_BM b WHERE a.bm = b.id;

6. 連線字串範例 (tnsnames.ora)

INPLAN:
(DESCRIPTION=
  (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.2.6)(PORT=1521)))
  (CONNECT_DATA=(SID=sa)(SERVER=DEDICATED)))

ERP:
(DESCRIPTION=
  (ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.3.25)(PORT=1528)))
  (CONNECT_DATA=(SID=TJTEST)))

關鍵提醒

Oracle 物件名稱（用戶、表、欄位）在四部分命名與 OPENQUERY 中必須大寫。
啟用 RPC：EXEC sp_serveroption 'LINK_NAME', 'rpc', 'true'; EXEC sp_serveroption 'LINK_NAME', 'rpc out', 'true';
大量資料同步建議用 OPENQUERY 或 SSIS，避免分散式交易複雜度。