Post reply

query results by email using xp_sendmail

  • December 22, 2010 at 4:59 pm

    #232706

    Hi

    I want to send query results by email using xp_sendmail procedure. I am able to send but the data formatted in wrong way.

    How to send query result with proper format?

    or

    how to attach a FORMATTED query result file in the email?

    Any help?

    Thanks,

    BMR

  • December 22, 2010 at 11:55 pm

    #1265790

    You can use reporting service Subscriptions

  • December 23, 2010 at 2:01 am

    #1265811

    Heres an example of a SELECT statement I use and it comes out nicely formatted :-):

    DECLARE @tableHTMLVARCHAR(MAX)

    DECLARE @tEmailListVARCHAR(MAX)

    DECLARE @tProfileVARCHAR(128)

    DECLARE @tEmailSubjectVARCHAR(2048)

    SELECT

    @tEmailList = email_address

    ,@tEmailSubject = CAST(@@SERVERNAME AS VARCHAR(128)) + ' - DB_Admin.dbo.usp_sel_InfoOS'

    ,@tableHTML = N'<H2>Database Report for Server: '

    + @@SERVERNAME + '</H2>'

    + N'<table border="1">'

    + N'<tr>

    <th>Host Name</th>

    <th>OS Name</th>'

    + CAST

    (

    (

    SELECT

    td = [HostName]

    ,''

    ,td = [OSName]

    ,''

    FROM [DB_Admin].[dbo].[tbl_InfoOS]

    FOR XML PATH('tr'), TYPE) AS NVARCHAR(MAX))

    + N'</table>

    This was generated by the <i>DB_Admin.dbo.usp_sel_InfoOS</i> Stored Procedure

    '

    FROM msdb.dbo.sysoperators

    WHERE [name] = 'DBA'

    EXEC msdb.dbo.sp_send_dbmail

    @profile_name = @tProfile

    ,@recipients = @tEmailList

    ,@subject = @tEmailSubject

    ,@body_format = 'html'

    ,@body = @tableHTML

  • December 23, 2010 at 11:10 am

    #1266027

    Thanks for you both.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply