?
Solved

Database Mail

Posted on 2012-03-14
3
Medium Priority
?
404 Views
Last Modified: 2012-03-14
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
0
Comment
Question by:VitaminD
  • 3
3 Comments
 
LVL 40

Expert Comment

by:lcohan
ID: 37721743
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';
0
 
LVL 40

Expert Comment

by:lcohan
ID: 37721750
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
0
 
LVL 40

Accepted Solution

by:
lcohan earned 2000 total points
ID: 37721783
--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;
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question