Project: automation report
Base data: BluePrism automation data
Architecture:
- create report as string with tab delimited using "for xml path('')"
- save the report in a table
- using BCP command to grab report from the table and save it on file system
- 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,''),'<','<'),'>','>')
email report as attachment from database
Comments