SQL Server 寫入文字檔：OLE Automation 預存程序實作

完整預存程序碼

IF EXISTS (SELECT * FROM dbo.sysobjects 
           WHERE id = OBJECT_ID(N'[dbo].[p_writeTextFile]') 
             AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[p_writeTextFile];
GO

CREATE PROCEDURE p_writeTextFile
    @filename VARCHAR(1000),  -- 完整路徑
    @text     VARCHAR(8000)   -- 寫入內容
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @obj INT, @err INT, @src VARCHAR(255), @desc VARCHAR(255);

    -- 1. 建立 FileSystemObject
    EXEC @err = sp_OACreate 'Scripting.FileSystemObject', @obj OUT;
    IF @err <> 0 GOTO ErrorHandler;

    -- 2. 開啟檔案 (8=Append, 1=Create if not exist)
    EXEC @err = sp_OAMethod @obj, 'OpenTextFile', @obj OUT, @filename, 8, 1;
    IF @err <> 0 GOTO ErrorHandler;

    -- 3. 寫入一行
    EXEC @err = sp_OAMethod @obj, 'WriteLine', NULL, @text;
    IF @err <> 0 GOTO ErrorHandler;

    -- 4. 釋放物件
    EXEC @err = sp_OADestroy @obj;
    RETURN;

ErrorHandler:
    EXEC sp_OAGetErrorInfo @obj, @src OUT, @desc OUT;
    SELECT CAST(@err AS VARBINARY(4)) AS ErrorCode, @src AS ErrorSource, @desc AS ErrorDesc;
    EXEC sp_OADestroy @obj;
END
GO

呼叫範例

EXEC p_writeTextFile 'C:\logs\app.log', '測試寫入 ' + CONVERT(VARCHAR, GETDATE(), 120);

關鍵參數說明

⚠️ 安全性與權限須知

• 需啟用 Ole Automation Procedures 伺服器設定：EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;
• SQL Server 服務帳戶需具備目標資料夾 寫入權限。
• 大量/高頻寫入建議改用 xp_cmdshell + echo、CLR 預存程序或外部 ETL 工具，效能與穩定性較佳。

錯誤處理模式

採用 GOTO ErrorHandler 集中處理，確保 sp_OADestroy 一定被呼叫，避免 COM 物件洩漏導致記憶體耗盡。