We help IT Professionals succeed at work.

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
Comment
Watch Question

lcohanDatabase Analyst
CERTIFIED EXPERT

Commented:
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';
lcohanDatabase Analyst
CERTIFIED EXPERT

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

http://www.experts-exchange.com/Software/Office_Productivity/Groupware/Outlook/Q_26826472.html?sfQueryTermInfo=1+10+30+dbmail+send+sp

--colored HTML
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_26963149.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
Database Analyst
CERTIFIED EXPERT
Commented:
--In your specific case would be:



declare @report_file_name varchar(30),
            @sql varchar(max),
            @emailbody varchar(4000)
      
      set @sql = N'SET NOCOUNT ON
                         Exec Billing.MerchantInfo @StartDate,@EndDate ,11'

      SET @report_file_name = 'Billing_MerchantInfo_at_' + convert(varchar(10),getdate(), 112) + '.txt'
      SET @emailbody = 'The result from EmployeeHireProcedure as today(yyyy.mm.dd):  '+ convert(varchar(10),getdate(), 102)
      EXEC msdb.dbo.sp_send_dbmail
            @profile_name = 'AdminAccount', -- mail profile here
            @recipients = 'lance@gmail.com',
            @subject = 'T-SQL Query Result',
            @body = @emailbody,
            @body_format = 'TEXT',
            @query = @sql,
            @attach_query_result_as_file = 1,
            @execute_query_database = 'YourDB', -- database name here where Billing.MerchantInfo SP exists
            @query_attachment_filename = @report_file_name,
            @query_result_header = 1,
            @query_result_no_padding = 1,
            @query_result_separator = '      ',
            @query_result_width = 1000;

Explore More ContentExplore courses, solutions, and other research materials related to this topic.