Sql-server – Alternative to xp_cmdshell for emailing a report as a CSV file

database-mailsql servert-sqltable variablexp-cmdshell

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.

Best Answer

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!).

Related Question