SQL Server 2005 資料同步至 Oracle：連結伺服器 + SQL Agent 實作

專案需將 SQL Server 2005 合約管理資料 (contract, contract_project) 同步至 Oracle 9i MIS 系統。採用 **連結伺服器 + SQL Agent 排程** 實作。

1. Oracle 端建立對應資料表

資料型別對應查詢：

SELECT * FROM msdb.dbo.MSdatatype_mappings;      -- SQL→Oracle 簡易對應
SELECT * FROM msdb.dbo.sysdatatypemappings;      -- 詳細跨 DBMS 型別對應

關鍵對應 (節錄)：

2. SQL Server 建立連結伺服器

EXEC sp_addlinkedserver 
  @server = 'ORA_MIS', 
  @srvproduct = 'Oracle', 
  @provider = 'OraOLEDB.Oracle',  -- 需安裝 Oracle OLE DB Provider
  @datasrc = 'ORCL';              -- TNS 服務名

EXEC sp_addlinkedsrvlogin 
  @rmtsrvname = 'ORA_MIS', 
  @useself = 'FALSE', 
  @locallogin = NULL, 
  @rmtuser = 'mis_user', 
  @rmtpassword = 'mis_pwd';

3. 同步預存程序 (範例：合併 UPSERT)

CREATE PROCEDURE usp_Sync_Contract_To_Oracle
AS
BEGIN
  SET NOCOUNT ON;
  -- 使用 OPENQUERY 執行 Oracle MERGE (需 Oracle 9i+)
  INSERT INTO OPENQUERY(ORA_MIS, 'SELECT CONTRACT_ID, COL1, COL2 FROM CONTRACT')
  SELECT CONTRACT_ID, COL1, COL2 FROM dbo.contract WHERE LastSync > @LastRun;
  -- 實際建議使用 MERGE 或分開 INSERT/UPDATE 邏輯
END

4. SQL Agent 排程作業

• 建立作業 → 步驟執行 usp_Sync_Contract_To_Oracle。
• 排程：每 15 分鐘/每小時/每日視業務需求。
• 啟用歷史記錄、失敗通知 (Database Mail)。

注意事項

• 連結伺服器分散式交易 (MSDTC) 需雙端啟用並配置防火牆。
• 大量資料同步建議使用 **SSIS 套件** (Oracle Destination/Attunity Connector) 效能更佳。
• 衝突處理：時間戳/版本號/觸發器記錄變更，避免覆蓋更新。