SQL Server 使用 BCP 與 xp_cmdshell 將查詢結果匯出為文字檔：系統預存程序實作

需求：在 T-SQL 中直接將任意查詢結果匯出為逗號分隔文字檔，檔名含日期戳。解決方案：封裝 bcp.exe 於系統預存程序，透過 xp_cmdshell 執行。

系統預存程序 sp_sql_query_to_file

CREATE PROCEDURE dbo.sp_sql_query_to_file
    @servername   VARCHAR(50),      -- 伺服器名稱
    @username     VARCHAR(50),      -- 登入帳號 (Windows 驗證傳空字串)
    @password     VARCHAR(50),      -- 密碼
    @sql_query    VARCHAR(2000),    -- 查詢語句 (字串條件內層用單引號)
    @filePath_name VARCHAR(1000),   -- 匯出路徑與檔名主體
    @date         DATETIME          -- 日期戳
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd VARCHAR(8000);

    SET @cmd = 'bcp "' + @sql_query + '" queryout "' +
               @filePath_name + '_' + CONVERT(VARCHAR(12), @date, 112) + '.txt" ' +
               '/t "," /w ' +                           -- 欄位逗號分隔、Unicode 寬字元
               '/S ' + @servername + ' ' +
               CASE WHEN ISNULL(@username,'') = '' THEN '' ELSE '/U ' + @username + ' ' END +
               '/P ' + ISNULL(@password,'');

    PRINT @cmd;  -- 除錯用
    EXEC master..xp_cmdshell @cmd;

    IF @@ERROR <> 0
        PRINT '匯出失敗，錯誤碼: ' + CAST(@@ERROR AS VARCHAR);
END

呼叫範例

EXEC dbo.sp_sql_query_to_file
    @servername = 'LOCALHOST',
    @username = 'sa',
    @password = 'P@ssw0rd',
    @sql_query = 'SELECT OrderID, OrderDate, CustomerID FROM Northwind.dbo.Orders WHERE OrderDate > ''2023-01-01''',
    @filePath_name = 'D:\Export\Orders',
    @date = GETDATE();
-- 產生 D:\Export\Orders_20240518.txt

關鍵參數說明

安全與權限提醒

• xp_cmdshell 需 sysadmin 或 proxy account 權限，**生產環境嚴謹評估風險**。
• 建議改用 **SSIS 匯出任務**、**PowerShell 腳本**、**SQLCLR** 或 **外部排程** 執行 BCP，避免在資料庫引擎內啟動外部進程。
• 查詢語句中表名**必須使用三段式命名** [Database].[Schema].[Table]，否則 BCP 內容無法解析。

結語

透過 xp_cmdshell 呼叫 bcp 可快速實現 T-SQL 內匯出文字檔，但安全性與穩定性較差。企業級應用建議採用 SSIS 或外部排程分離匯出邏輯。

1. 上一篇文章： 識別真實索引與自動統計資料：INDEXPROPERTY 與系統目錄查詢實務 (補充版)
2. 下一篇文章： SQL Server 2008 透明資料加密 (TDE) 實作實戰：從備份檔加密到還原驗證

關於SqlServer

SSIS 中使用 Analysis Services 執行 DDL 任務自動化管理 OLAP 物件

任務簡介 Analysis Services 執行 DDL

SQL Server 2005 CLR 集成與 ADO.NET 2.0 深度實戰：擴展數據庫可編程邊界

CLR 集成：托管代碼進入數據庫核心 SQL Server

Azure VM 部署 SQL Server 網路不通？防火牆開啟 1433 埠完整圖文教學

問題背景 Azure 虛擬機器預設**關閉所有輸入埠**，S

SQL Server 索引與視圖完整指南：建立、管理與效能優化

第一部分：索引深度解析 索引概述與核心概念 資料庫索引如同