Send Email with Query Contents
If you want to send the results of a query as an email attachment, make sure you have Database Mail set up, then you can do the following:
create procedure util.SendEmailWithQueryContents
(
@DBMailProfileName sysname
,@SemiColonSeparatedEmails varchar(max)
,@Subject nvarchar(255)
,@Body nvarchar(max)
,@QueryText nvarchar(max)
,@DatabaseName sysname
,@AttachmentFileName nvarchar(255)
,@IncludeHeader bit = 1
,@Delimiter char(1) = ','
,@IsBodyHtml bit = 0
)
as
begin
declare @BodyFormat varchar(20);
if @IsBodyHtml = 1
set @BodyFormat = 'HTML';
else
set @BodyFormat = 'TEXT';
exec msdb.dbo.sp_send_dbmail
@profile_name = @DBMailProfileName
,@recipients = @SemiColonSeparatedEmails
,@subject = @Subject
,@body = @Body
,@query = @QueryText
,@execute_query_database = @DatabaseName
,@attach_query_result_as_file = 1
,@query_attachment_filename = @AttachmentFileName
,@query_result_header = @IncludeHeader
,@query_result_separator = @Delimiter
,@query_result_no_padding = 1
,@body_format = @BodyFormat;
end;
go