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!

• Lowell

  SSC Guru

  Points: 323524

  More actions

  April 1, 2016 at 8:27 am

  #1869166

  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

  April 1, 2016 at 10:22 am

  #1869228

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

• Lowell

  SSC Guru

  Points: 323524

  More actions

  April 1, 2016 at 11:09 am

  #1869239

  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

  April 1, 2016 at 12:32 pm

  #1869248

  Thanks

• SumanMalhotraK

  Grasshopper

  Points: 19

  More actions

  October 17, 2018 at 4:50 am

  #2010009

  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