SQL Server 預存程序呼叫 COM 組件：sp_OA 系列系統預存程序實戰

動機

T-SQL 功能強大但有限制 (如檔案系統操作、複雜數學運算、外部 API 呼叫)。透過 sp_OACreate 等 OLE 自動化預存程序，可在 T-SQL 中實例化並呼叫 COM 組件，擴展資料庫能力。

建立測試 COM 組件 (VB6 範例)

' Project: testSQLCOM, Class: TestMath
Public Function AddMe(a As Long, b As Long) As Long
    AddMe = a + b
End Function

編譯為 testSQLCOM.dll 並註冊至系統 (regsvr32 testSQLCOM.dll)。

T-SQL 呼叫腳本

DECLARE @i INT, @intRet INT, @intRetCode INT
DECLARE @strErr VARCHAR(255), @strErr1 VARCHAR(255)

-- 1. 建立 COM 實例
EXEC @intRetCode = sp_OACreate 'testSQLCOM.TestMath', @i OUT
IF @intRetCode <> 0 BEGIN
    EXEC sp_OAGetErrorInfo @i, @strErr OUT, @strErr1 OUT
    PRINT '建立失敗: ' + @strErr + ' ' + @strErr1
    RETURN
END

-- 2. 呼叫方法 (第三參數為 OUTPUT 接收傳回值)
EXEC @intRetCode = sp_OAMethod @i, 'AddMe', @intRet OUT, 100, 200
IF @intRetCode <> 0 BEGIN
    EXEC sp_OAGetErrorInfo @i, @strErr OUT, @strErr1 OUT
    PRINT '呼叫失敗: ' + @strErr + ' ' + @strErr1
    EXEC sp_OADestroy @i
    RETURN
END

PRINT '結果: ' + STR(@intRet)  -- 輸出 300

-- 3. 釋放資源
EXEC sp_OADestroy @i

核心系統預存程序說明

安全與效能警示

• 需啟用 Ole Automation Procedures：sp_configure 'Ole Automation Procedures', 1; RECONFIGURE (預設關閉)。
• 權限管控：僅授予必要登入 EXECUTE 權限於 sp_OA*，嚴禁公開給應用帳號。
• 效能影響：COM 呼叫為行程外處理，上下文切換開銷大，嚴禁在高頻交易/觸發器中使用。
• 穩定性風險：COM 元件崩潰可能導致 SQL Server 服務不穩，建議隔離於獨立程序並完善錯誤處理。
• 64 位元環境：需確保 COM 組件為 64 位元或啟用 WoW64 相容模式。

適用場景

低頻率管理任務：產生報表檔案 (Excel/Word)、發送郵件、呼叫舊有 VB6/VC++ 業務邏輯 DLL、存取檔案系統中繼資料。核心交易邏輯仍建議純 T-SQL 或 CLR 整合 (SQL 2005+)。