Send Multiple query results as multiple csv files in a single email

Question

Send Multiple query results as multiple csv files in a single email

SQL!$@w$0ME

SSChampion

Points: 12349
More actions
April 1, 2016 at 8:08 am

#312897

Is there a query any one can provide to send multiple query results as multiple csv files in a single email.
I am able to setup a single query result as a single attachment with the help of the following link.
https://www.simple-talk.com/blogs/2015/06/01/sending-query-results-to-excel-through-e-mail/
Many thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323504 More actions · Answer 1

you'll need to have a separate process save each query to disk on the server, and then the email will not have a query, only attachments.

i currently use a CLR, here's a code example:

DECLARE @TheFilename VARCHAR(100) = 'tChartPreview_'

+ CONVERT(VARCHAR, Getdate(), 112) + '_'

+ Replace(CONVERT(VARCHAR, Getdate(), 114), ':', '-')

+ '.csv'

EXEC DBA_Utilities.dbo.CLR_ExportQueryToCSV

@QueryCommand ='SELECT * FROM #MeasuresTable ORDER BY [DOS]',

@FilePath = 'L:\SSIS\DBAExports\',

@FileName = @TheFilename,

@IncludeHeaders = 0;

SELECT @TheFilename = 'L:\SSIS\DBAExports\' + @TheFilename;

DECLARE @TheFilename2 VARCHAR(100) = 'ChartDetails_'

+ CONVERT(VARCHAR, Getdate(), 112) + '_'

+ Replace(CONVERT(VARCHAR, Getdate(), 114), ':', '-')

+ '.csv'

EXEC DBA_Utilities.dbo.CLR_ExportQueryToCSV

@QueryCommand ='SELECT * FROM #MeasuresDetailsTable ORDER BY [DOS]',

@FilePath = 'L:\SSIS\DBAExports\',

@FileName = @TheFilename,

@IncludeHeaders = 0;

SELECT @TheFilename2 = 'L:\SSIS\DBAExports\' + @TheFilename2;

DECLARE @AllFilenames varchar(max) = @TheFilename + ';' + @TheFilename2

EXECUTE msdb.dbo.sp_send_dbmail

@profile_name='Default Database Mail',

@recipients=@EmailGroup,

@subject = @mySubject,

@body = @HTMLBody,

@body_format = 'HTML',

@file_attachments = @AllFilenames;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 2

Thanks Lowell. Can you please provide the code for 'CLR_ExportQueryToCSV'.

Lowell SSC Guru Points: 323504 More actions · Answer 3

SQL!$@w$0ME (4/1/2016)
Thanks Lowell. Can you please provide the code for 'CLR_ExportQueryToCSV'.

below is an updated version of it, i posted the original version of it on http://sqlclrexport.codeplex.com/ years ago.

SQLCLRExport.zip

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SQL!$@w$0ME SSChampion Points: 12349 More actions · Answer 4

SQL!$@w$0ME

SSChampion

Points: 12349

April 1, 2016 at 12:32 pm

#1869248

Thanks

SumanMalhotraK Grasshopper Points: 19 More actions · Answer 5

Lowell - Friday, April 1, 2016 11:09 AM
When clicking on SQLCLRExport.zip , its saying 'Page can't be displayed'
Kindly suggest.
SQL!$@w$0ME (4/1/2016)
Thanks Lowell. Can you please provide the code for 'CLR_ExportQueryToCSV'.
below is an updated version of it, i posted the original version of it on http://sqlclrexport.codeplex.com/ years ago.SQLCLRExport.zip