SQL Server 查詢結果直接匯出 Excel：BCP + xp_cmdshell 無 Office 元件方案

需將多張表的取樣資料分別匯出 Excel 時，手動「另存新檔」極不效率。利用 xp_cmdshell 呼叫 BCP 工具，可在 T-SQL 中直接產生 .xls 檔，無需安裝 Office 元件。

預存程序：ExportFile

CREATE PROC ExportFile
    @QuerySql   VARCHAR(MAX),   -- 查詢語句 (含 FROM 子句)
    @Server     VARCHAR(20),    -- 目標伺服器 . 或 127.0.0.1 表本機
    @User       VARCHAR(20),
    @Password   VARCHAR(20),
    @FilePath   NVARCHAR(100) = 'C:\ExportFile.xls'
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @TmpTable VARCHAR(50) = '[##Table_' + CONVERT(VARCHAR(36), NEWID()) + ']';
    DECLARE @Sql VARCHAR(MAX), @DataSource VARCHAR(MAX) = '';

    BEGIN TRY
        -- 1. 判斷是否跨伺服器
        IF @Server NOT IN ('.','127.0.0.1')
            SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source='+@Server+';User ID='+@User+';Password='+@Password+''').';

        -- 2. 將查詢結果導入暫存表 (加上資料庫前綴)
        SET @Sql = REPLACE(@QuerySql, ' FROM ', ' INTO ' + @TmpTable + ' FROM ' + @DataSource);
        EXEC(@Sql);

        -- 3. 組合欄位名與資料列 (轉為 NVARCHAR 避免型別衝突)
        DECLARE @Cols VARCHAR(MAX) = '', @Data VARCHAR(MAX) = '';
        SELECT @Cols = @Cols + ',''' + name + '''',
               @Data = @Data + ',CONVERT(NVARCHAR,[' + name + '])'
        FROM tempdb.sys.columns WHERE object_id = OBJECT_ID('tempdb..' + @TmpTable);

        SET @Cols = 'SELECT ' + SUBSTRING(@Cols, 2, LEN(@Cols));
        SET @Data = 'SELECT ' + SUBSTRING(@Data, 2, LEN(@Data)) + ' FROM ' + @TmpTable;

        -- 4. 啟用 xp_cmdshell 並呼叫 BCP
        EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

        DECLARE @Cmd NVARCHAR(4000) = 
            'bcp "' + @Cols + ' UNION ALL ' + @Data + '" queryout "' + @FilePath + '" -c -T';
        EXEC sys.xp_cmdshell @Cmd;

        EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
        EXEC('DROP TABLE ' + @TmpTable);
    END TRY
    BEGIN CATCH
        IF OBJECT_ID('tempdb..' + @TmpTable) IS NOT NULL
            EXEC('DROP TABLE ' + @TmpTable);
        EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
        SELECT ERROR_MESSAGE() AS ErrMsg;
    END CATCH
END;

使用範例

-- 本機匯出
EXEC ExportFile 
    'SELECT TOP 100 * FROM AdventureWorks2012.Person.Person',
    '.', 'sa', 'pwd', 'C:\Temp\Person.xls';

-- 跨伺服器匯出 (需連結伺服器或 OPENDATASOURCE 權限)
EXEC ExportFile 
    'SELECT * FROM RemoteDB.dbo.Orders',
    '192.168.1.50', 'remote_user', 'remote_pwd', 'C:\Temp\Orders.xls';

關鍵點與限制

• 權限：執行帳戶需具備 xp_cmdshell、BCP 執行權限，且目標資料夾寫入權限。
• 編碼：`-c` 參數使用字元模式 (預設代碼頁)，中文環境建議加 `-C 65001` (UTF-8) 或 `-C 936` (GBK)。
• 大數據量：BCP 串流寫入，記憶體壓力小，適合百萬列以上匯出。
• 安全性：生產環境建議關閉 xp_cmdshell，改用 SSIS 或應用層匯出。