Alternative to xp_cmdshell for emailing a report as a CSV file

Question

I've got an issue that I could do with some ideas as to how to achieve what's needed without using (or enabling) xp_cmdshell if possible.

I know that xp_cmdshell itself poses risks, even with a proxy account, however - in our environment it's disabled and convincing the IT Manager to enable it is going to be difficult at best.

The issue I'm having is that I have a stored procedure, it looks up data from various tables and puts it all into a table variable.

I want to do the following:

• Export the content of the table variable to a CSV file.
• Attach the created CSV file to an e-mail and send this to a specific address.

This needs to happen automatically without user input of any kind, the e-mail side of it will be provided by variables in the same stored procedure.

I've thought of 2 ways to potentially do this, both use bcp and xp_cmdshell.

The first method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to fire off an SSIS Package or SQL Agent Job which will query the temp table, export to CSV and e-mail for me.

The second method was to create a temporary table, select the records I want from the table variable into the temp table, then use bcp to select the temporary table into a CSV file, then use sp_send_dbmail to send it.

A third alternative might be to fire another SP/function and have it do either of the above methods instead, keeping the new functionality separate - but this is simply a thought to keep things simple.

So, what I'm asking for is any ideas as to how I can achieve this safely, preferably without using xp_cmdshell. I can't use PowerShell or a similar technology as no-one here knows PowerShell, although I'm familiar with VB.NET, however I'm not sure how that helps (if at all).

If you need more information to be able to help, please let me know.

Answer 1

Since using sp_send_dbmail is an option here, I don't see why you need to export anything given that sp_send_dbmail can run a query and include the results, either in the body or as an attachment. I would first try to make use of the @query, @attach_query_result_as_file, @query_attachment_filename, @query_result_header, @query_result_width, @query_result_separator, @query_no_truncate, and @query_result_no_padding parameters. Please keep in mind this note from that MSDN page (i.e. the link above) regarding the query to run:

Note that the query is executed in a separate session, so local variables in the script calling sp_send_dbmail are not available to the query.

Because of this, and assuming that you won't have this process running more than once at any particular moment, you should dump the desired rows from the Table Variable into a Global Temporary Table (i.e. starting with ## instead of #).

I believe sp_send_dbmail uses Service Broker and is asynchronous. This would seem to allow for the possibility of the global temporary table not existing when the query in @query finally gets executed, if the session that executed sp_send_dbmail ends before the query in @query starts. In this case, you can do either:

• add a WAITFOR DELAY '00:00:10.000'; to add a 10-second delay before the calling session ends.

• instead of using a global temporary table, use NEWID() to construct a unique table name for the purpose of dumping that data into tempdb in a real table that will exist for as long as you need it to. Then you can drop it at the end of the query that you submit for the report. The bigger issue is that now Dynamic SQL is needed for the Table name, yet that won't work with a Table Variable since that can't be passed into Dynamic SQL. We need a known and consistent object name to insert the rows from the Table Variable into the real Table. Thankfully, a SYNONYM can provide a consistent name in the current context that points to the real Table in tempdb since they can be created in Dynamic SQL. That gives us the following (and you can run the following to see that it works, though I did not implement the final DROP of the real Table in 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, ...;
  

  If the process fails after creating the Table but before the DROP statement is called, no problem: tempdb is created from the empty model Database every time the SQL Server service restarts (which is why I am suggesting to create the Tables in tempdb!).

Answer 2

Since no one else has mentioned this as a possibility, you can do this all directly via TSQL commands.

First, you can directly write out to a file via either a linked server (if the file will have a static path) or via OPENROWSET or OPENDATASOURCE functions if the files are more dynamic in nature. Details on how to do this for excel files can be found here: https://support.microsoft.com/en-us/kb/306397

Details for flat files such as text or csvs can be found here: http://blog.waynesheffield.com/wayne/code-library/ad-hoc-querying/ad-hoc-querying-text-files/

Once you have created the file(s) to your liking, you can send an email via sp_send_dbmail, using the @file_attachments parameter to specify the file. Note that by default there's a 1MB file attachment limit, so if these files will be larger, you will want to adjust the mail profile accordingly (e.g. using sysmail_configure_sp passing the MaxFileSize parameter).

I'm not endorsing this approach over any others, but here's another option for you to chew on.

John

Answer 3

You could use SSIS to do this relatively easily, you would just bypass the table variable or temp table and use a flat file destination (The CSV in your case). You can then send the email directly from within the SSIS workflow using the email task and specifying the CSV as an attachment.

This would have the added benefit of being able to use logging from SSIS and the other plumbing like archiving/deleting CSV's once sent if needed etc.

Alternatively if you really must use xp_cmdshell you can always have a procedure which turns it on, does the processing you want to, then turns it off again.

Mat

Answer 4

Why not install SSRS (or check if there is an existing installation available) and schedule a report? The stored procedure would just be responsible for returning the data.

The converting to CSV and emailing would be done by SSRS.

Once the schedule is set up you can run the report and email subscription on demand if needed by executing the relevant procedure in the ReportingServices database.

(If the recipient email addresses are entirely dynamic and unknown in advance this will require a data driven subscription though, which is Enterprise Edition. If this is not available you'd probably need a different approach.)

Answer 5

I have a solution that works, although it's perhaps not as elegant as it could have been. I use the SP to generate a global temp table and then use sp_start_job to start an SQL Agent Job which in turn takes the global temp table, outputs it as CSV, e-mails it and then finally drops the global temp table (as this is created by the SP in the first place but only if the condition in the SP is met).

Thank you all very much for your help and suggestions, I suspect this won't be the last time I'll be asked to do something like this so will bookmark this page.

Answer 6

I've run a weekly report for management using PowerShell to execute the sql and e-mail the report to them. Here's a generic example

# Weekly Report
# Runs every Monday using Task Scheduler of Windows OS.
# Sends report to manager

$sql =
@"
EXEC YourStoredProcedure;
"@

$FilePath = "C:\Users\UserName"

$OutFile = Join-Path -path $FilePath -childPath ("YourReport_" + (get-date).toString('yyyyMMdd_hhmmtt') + ".csv")


try {
    invoke-sqlcmd -query $sql -ServerInstance "YourSQLInstance" -Database "Database" -Username "UserName" -Password "" | Export-Csv $Outfile  -NoTypeInformation
    send-mailmessage -to "Joe User <[email protected]>" -from "Ronald Dameron <[email protected]>" -subject "Your Report" -SmtpServer "mail.domain.com" -Attachments $OutFile
} catch {
    send-mailmessage -to "Ronald Dameron <[email protected]>" -from "Ronald Dameron <[email protected]>" -subject "Report run FAILED!" -SmtpServer "mail.domain.com" -Body "Job failed to send weekly report." -Priority High
}