xp_cmdshell 的替代方法，用於將報告作為 CSV 文件通過電子郵件發送

我有一個問題，如果可能的話，我可以用一些想法來解決如何在不使用（或啟用）的情況下實現所需的東西xp_cmdshell。

我知道它xp_cmdshell本身會帶來風險，即使使用代理帳戶也是如此 - 在我們的環境中它被禁用並且說服 IT 經理啟用它充其量是困難的。

我遇到的問題是我有一個儲存過程，它從各種表中查找數據並將其全部放入表變數中。

我想做以下事情：

• 將表變數的內容導出到 CSV 文件。
• 將創建的 CSV 文件附加到電子郵件並將其發送到特定地址。

這需要在沒有任何類型的使用者輸入的情況下自動發生，它的電子郵件端將由同一儲存過程中的變數提供。

我想到了兩種可能做到這一點的方法，都使用 bcp 和xp_cmdshell.

第一種方法是創建一個臨時表，從表變數中選擇我想要的記錄到臨時表中，然後使用 bcp 啟動一個 SSIS 包或 SQL 代理作業，它將查詢臨時表，導出到 CSV 和電子表格給我發郵件。

第二種方法是創建一個臨時表，從表變數中選擇我想要的記錄到臨時表中，然後使用 bcp 將臨時表選擇為一個 CSV 文件，然後使用sp_send_dbmail發送。

第三種選擇可能是觸發另一個 SP/函式，並讓它執行上述任何一種方法，保持新功能分開 - 但這只是為了保持簡單的想法。

所以，我要的是關於如何安全地實現這一點的任何想法，最好是不使用xp_cmdshell. 我不能使用 PowerShell 或類似的技術，因為這裡沒有人知道 PowerShell，雖然我熟悉 VB.NET，但我不確定這有什麼幫助（如果有的話）。

如果您需要更多資訊來提供幫助，請告訴我。

由於在這裡使用sp_send_dbmail是一個選項，我不明白為什麼需要導出任何sp_send_dbmail可以執行查詢並包含結果的內容，無論是在正文中還是作為附件。我會首先嘗試使用@query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @query_result_separator, @query_no_truncate, 和@query_result_no_padding參數。請記住該 MSDN 頁面（即上面的連結）中有關要執行的查詢的註釋：

請注意，查詢是在單獨的會話中執行的，因此呼叫 sp_send_dbmail 的腳本中的局部變數對查詢不可用。

正因為如此，並且假設您在任何特定時刻不會多次執行此程序，您應該將所需行從表變數轉儲到全域臨時表中（即以##代替開頭#）。

我相信sp_send_dbmail使用 Service Broker 並且是非同步的。@query如果執行的會話sp_send_dbmail在查詢開始之前結束，這似乎允許在最終執行查詢時全域臨時表不存在的可能性@query。在這種情況下，您可以執行以下任一操作：

• 添加 aWAITFOR DELAY '00:00:10.000';在呼叫會話結束前添加 10 秒延遲。
• 而不是使用全域臨時表，而是使用NEWID()構造一個唯一的表名，以便將該數據轉儲到tempdb一個真實表中，只要您需要它就會存在。然後，您可以將其放在為報告送出的查詢的末尾。更大的問題是，現在表名稱需要動態 SQL，但這不適用於表變數，因為它不能傳遞給動態 SQL。我們需要一個已知且一致的對象名稱來將表變數中的行插入到真實表中。值得慶幸的是，SYNONYM可以在目前上下文中提供一致的名稱，指向真實的表tempdb因為它們可以在動態 SQL 中創建。這為我們提供了以下內容（您可以執行以下內容以查看它是否有效，儘管我沒有在DROP中實現真實表格的最終結果tempdb）：

SET NOCOUNT ON;

DECLARE @TableName NVARCHAR(70) = N'tempdb.dbo.[Report_'
                                 + CONVERT(NVARCHAR(36), NEWID()) + N']';
DECLARE @SQL NVARCHAR(MAX);

SET @SQL = N'CREATE TABLE ' + @TableName + N' ([Col1a] INT);';
EXEC (@SQL);

SET @SQL = N'CREATE SYNONYM dbo.TempDump FOR ' + @TableName + N';';
EXEC (@SQL);

DECLARE @SomeTableVar TABLE ([Col1b] INT);
INSERT INTO @SomeTableVar ([Col1b]) VALUES (1), (20), (34), (4444);

INSERT INTO dbo.TempDump ([Col1a])
 SELECT Col1b
 FROM   @SomeTableVar;

SELECT * FROM dbo.TempDump; -- only needed for debug

SET @SQL = N'DROP SYNONYM dbo.TempDump;';
EXEC (@SQL);


-- create the report query, ending with the DROP statement
DECLARE @ReportSQL NVARCHAR(MAX) = N'';
SET @ReportSQL += N'my report, sent as @query, using ' + @TableName + N';';
SET @ReportSQL += NCHAR(0x0D) + NCHAR(0x0A) + N'DROP TABLE ' + @TableName + N';';
print @ReportSQL;

-- EXEC  sp_send_dbmail ..., @query = @ReportSQL, @attach_query_result_as_file = 1,
--                      @query_result_separator = N',', @query_no_truncate = 1, ...;

如果該過程在創建表之後但在DROP呼叫語句之前失敗，則沒有問題：每次 SQL Server 服務重新啟動時tempdb從空model數據庫創建（這就是我建議在中創建表的原因tempdb！）。

由於沒有其他人提到這是一種可能性，因此您可以直接通過 TSQL 命令完成這一切。

首先，您可以通過連結伺服器（如果文件將具有靜態路徑）或通過 OPENROWSET 或 OPENDATASOURCE 函式（如果文件本質上更動態）直接寫入文件。可以在此處找到有關如何對 excel 文件執行此操作的詳細資訊：https: //support.microsoft.com/en-us/kb/306397

可在此處找到文本或 csv 等平面文件的詳細資訊：http: //blog.waynesheffield.com/wayne/code-library/ad-hoc-querying/ad-hoc-querying-text-files/

根據自己的喜好創建文件後，您可以通過sp_send_dbmail發送電子郵件，使用 @file_attachments 參數指定文件。請注意，預設情況下，文件附件限制為 1MB，因此如果這些文件較大，您將需要相應地調整郵件配置文件（例如，使用sysmail_configure_sp傳遞 MaxFileSize 參數）。

我並不贊同這種方法而不是其他任何方法，但這裡有另一種選擇供您參考。

約翰

引用自：https://dba.stackexchange.com/questions/143687