asked on

Database Mail

Is there a way by using which we can use the Database mail service to send the result of a stored procedure that will be scheduled to run at a specific time each day? If yes, then where can we schedule it and how we can send the result in HTML format?

How do I return the info from this stored procedure
How do I properly format this info

USE msdb
Exec msdb.dbo.sp_send_dbmail
@profile_name = 'AdminAccount',
@recipients = 'lance@gmail.com',
@subject = 'T-SQL Query Result',
@body = 'The result from EmployeeHireProcedure',
@execute_query_database = 'msdb',
@query = 'Exec Billing.MerchantInfo @StartDate,@EndDate ,11'

Thanks In Advance for any suggestions

lcohan

It is possible and please see a few examples below - you can replace the query with EXEC sproc_name of course but the same idea:

To send query output as attachemnt:

eclare @report_file_name varchar(30),
            @sql varchar(max),
            @emailbody varchar(4000)

      set @sql = N'SET NOCOUNT ON
                        SELECT whatever FROM whatever_table WHERE whatever'

      SET @report_file_name = 'file_list_at_' + convert(varchar(10),getdate(), 112) + '.txt'
      SET @emailbody = 'Here is the list you wanted as today(yyyy.mm.dd): '+ convert(varchar(10),getdate(), 102)
      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Mail Profile', -- mail profile here
            @recipients='user@mail.com',
            @subject = 'This is a TEST email',
            @body = @emailbody,
            @body_format = 'TEXT',
            @query = @sql,
            @attach_query_result_as_file = 1,
            @execute_query_database = 'YourDB', -- your database name here
            @query_attachment_filename = @report_file_name,
            @query_result_header = 1,
            @query_result_no_padding = 1,
            @query_result_separator = '      ',
            @query_result_width = 1000;

To send attachmet only:

      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'Mail Profile', -- mail profile here
            @recipients='user@mail.com',
            @subject = 'Your image file has arrived...',
            @body = 'Here is your image file - see attached',
            @body_format = 'TEXT',
            @file_attachments = 'E:\PicturesFolder\Pic1.jpg';

lcohan

To send HTML from SQL query or SQL stored proc please see code from my solution for:

https://www.experts-exchange.com/questions/26826472/sp-send-dbmail-format.html?sfQueryTermInfo=1+10+30+dbmail+send+sp

--colored HTML
https://www.experts-exchange.com/questions/26963149/SQL-e-mail-loop-through-returned-records.html

create table #test (column1 int, column2 text, column3 text, column4 text)
insert into #test SELECT 1 ,'text2','text3','text4'
insert into #test SELECT 2 ,'code2','code3','code4'

DECLARE @title nvarchar(200)
DECLARE @tableHTML nvarchar(MAX)
SET @tableHTML =
N'<style type="text/css">h2, body {font-family: Arial, verdana;} table{font-size:12px; border-collapse:collapse;}
td{background-color:#F1F1F1; border:1px solid black; padding:4px;} th{background-color:#99CCFF; padding:4px;}
h4{font-family: "Courier New", Courier, monospace; font-size: 11px;} </style>'

SET @title = 'Summary Report - FunctionCode'
SET @tableHTML = @tableHTML +
N'<H1>' + @title + '</H1>' +
N'<table border="1">' +
N'<tr style="color:green;background-color:purple;">' +
N'<th>Column1</th>' +
N'<th>Column2</th>' +
N'<th>Column3</th>' +
N'<th>Column4</th>' +
CAST(
(SELECT
td = Column1 ,'',
td = Column2,'',
td = Column3,'',
td = Column4,''
FROM #test WHERE column1 in (1,2)
FOR XML PATH('tr'), TYPE)
AS NVARCHAR(MAX) )
+ N'</table>' ;

IF @tableHTML is not null
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'sqldba',
@recipients='lcohan@web.com',
@subject = @title,
@body = @tableHTML ,
@body_format = 'HTML' ;
END

drop table #test

ASKER CERTIFIED SOLUTION

lcohan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial