Send report in the attachment from database

Project: automation report

Base data: BluePrism automation data

Architecture:

  1. create report as string with tab delimited using “for xml path(”)”
  2. save the report in a table
  3. using BCP command to grab report from the table and save it on file system
  4. send email with the report in the attachment using sp_send_dbmail

Key notes in proc:

  • declare @filename nvarchar(255),@subject nvarchar(255);
  • select @subject = @reportName+’ (‘+@sun+’ – ‘+@sat+’)’, @filename = ‘\\xxx.xxxx.ca\Backup\’+@subject+’.xls’;
  • varchar(max) for long string
    declare @sql varchar(max), but script length longer than 8000, the string would be truncated, so remember to write code like this:
    set @sql = CAST(” AS varchar(MAX)) + ‘…’
  • BCP
    set @bcp =’bcp “select top 1 Report from BusinessSuite.dbo.tbl_Email_Report where ReportID=161 order by rptid desc” queryout ‘+@filename+’ -c -t -T’
    exec xp_cmdshell @bcp
    Note: use fully qualified table name, bcp is a shell command
  • Send mail
    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = null,
    @recipients = @email_list,  — format: “name 1″ <email@gmail.com>;”name 2” <email2@mail.com; …
    @subject = @subject,
    @body = @report,
    @importance = ‘HIGH’,
    @body_format =’HTML’,
    @file_attachments = @filename
  • Get mail list from table
    select @EMAIL_LIST=replace(replace(STUFF((
    select ‘;”‘+case when coalesce(Name,”)=” then email else Name end+'” <‘+email+’>’ from [dbo].[tbl_Email_Report_List] where ReportID=161 and Active=1 for xml path(”)
    ),1,1,”),’&lt;’,'<‘),’&gt;’,’>’)

email report as attachment from database

Leave a Reply

Your email address will not be published. Required fields are marked *