需將多張表抽樣導出 Excel?手工「Save Results As」太慢。本存儲過程使用 bcp + xp_cmdshell 直接導出含欄位名的 .xls 檔,無需安裝 Office,支援遠程伺服器查詢。
CREATE PROC ExportToExcel
@QuerySql NVARCHAR(MAX), -- 查詢 SQL (含資料庫名)
@Server VARCHAR(20) = '.', -- 目標伺服器,本機用 '.'
@User VARCHAR(20) = NULL, -- SQL 登入帳號
@Password VARCHAR(20) = NULL, -- 密碼
@FilePath NVARCHAR(200) = 'C:\Export\ExportFile.xls' -- 輸出路徑 (伺服器端)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @TmpTable NVARCHAR(50) = '##Tmp_' + REPLACE(CONVERT(VARCHAR(36), NEWID()), '-', '');
DECLARE @Sql NVARCHAR(MAX), @DataSource NVARCHAR(MAX) = '';
DECLARE @Columns NVARCHAR(MAX) = '', @Data NVARCHAR(MAX) = '';
DECLARE @Cmd NVARCHAR(4000);
BEGIN TRY
-- 1. 遠程資料來源字串
IF @Server NOT IN ('.', '127.0.0.1', '(local)')
SET @DataSource = 'OPENDATASOURCE(''SQLOLEDB'',''Data Source=' + @Server + ';User ID=' + @User + ';Password=' + @Password + ''').';
-- 2. 將查詢結果存入全域暫存表
SET @Sql = REPLACE(@QuerySql, ' FROM ', ' INTO ' + @TmpTable + ' FROM ' + @DataSource);
PRINT @Sql;
EXEC (@Sql);
-- 3. 組裝欄位名列 (BCP 無欄位名需自行 UNION)
SELECT @Columns = @Columns + ',''' + name + '''',
@Data = @Data + ',CONVERT(NVARCHAR(MAX),[' + name + '])'
FROM tempdb.sys.columns
WHERE object_id = OBJECT_ID('tempdb..' + @TmpTable);
SET @Columns = 'SELECT ' + STUFF(@Columns, 1, 1, '');
SET @Data = 'SELECT ' + STUFF(@Data, 1, 1, '') + ' FROM ' + @TmpTable;
-- 4. 啟用 xp_cmdshell 並執行 BCP
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
SET @Cmd = 'bcp "' + @Columns + ' UNION ALL ' + @Data + '" queryout "' + @FilePath + '" -c -T -C 65001';
PRINT @Cmd;
EXEC master..xp_cmdshell @Cmd;
-- 5. 關閉 xp_cmdshell、清理暫存表
EXEC sp_configure 'xp_cmdshell', 0; RECONFIGURE;
EXEC sp_configure 'show advanced options', 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;
EXEC sp_configure 'show advanced options', 0; RECONFIGURE;
THROW; -- 重拋錯誤
END CATCH
END
-- 本機查詢
EXEC ExportToExcel
@QuerySql = 'SELECT TOP 100 * FROM AdventureWorks2019.Person.Person',
@FilePath = 'C:\Export\Person.xls';
-- 遠程伺服器查詢
EXEC ExportToExcel
@QuerySql = 'SELECT * FROM RemoteDB.dbo.Orders WHERE OrderDate >= ''2024-01-01''',
@Server = '192.168.1.50',
@User = 'report_user',
@Password = 'P@ssw0rd',
@FilePath = 'D:\Exports\Orders_2024.xls';
##Tmp_ 全域暫存表避免並發衝突OPENDATASOURCE 臨時連結遠程伺服器,無需建立 Linked ServerCONVERT(NVARCHAR(MAX), col) 統一類型避免 UNION 型別衝突bcp -c -C 65001 字元模式 + UTF-8 編碼,Excel 開啟不亂碼-T 信任連線 (Windows 驗證);SQL 驗證改 -U user -P pwdsysadmin 或 securityadmin (設定 xp_cmdshell)