sending email sp_OAsetproperty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sending email sp_OAsetproperty

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2008-06-18 : 05:28:43

i'm using

EXEC @hr = sp_OASetProperty @object, 'HTMLBody', 'some text'

for sending email from my sql sever.

how can i make my content dynamic. So instead of 'some text' i would like to have data (results) from database; eg.: select * from table1.

is this possible anyhow?

thank you in advance

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2008-06-18 : 07:59:18

Have a look at this procedure...it's basically building an HTML script dynamically and then passing that as the email text to the mailer procedure:

ALTER PROCEDURE [dbo].[proc_replication_latency_mailer]
  @EmailRecipients varchar(max), --> Semicolon-separated list of recipients
  @AcceptedLatency int = 15
AS

SET NOCOUNT ON

BEGIN

DECLARE
  @EmailSubject varchar(300),
  @EmailBody varchar(max)

IF EXISTS (SELECT 1 FROM dbo.replication_latency WHERE IsChecked IS NULL AND COALESCE(OverallLatency, 1000) > @AcceptedLatency)
  BEGIN

    SET @EmailSubject = 'Replication latency report'
    SET @EmailBody = '
      <HTML>
      <HEAD>
      <TITLE>' + @EmailSubject + '</TITLE>
      <STYLE TYPE="text/css">
        table { border: 1px solid #666666; border-collapse: collapse; }
        th    { border: 1px solid #666666; font-family: verdana; font-size: 10px; background-color: #CCCCCC; font-weight: bold; vertical-align: top; padding: 2px; }
        td    { border: 1px solid #666666; font-family: verdana; font-size: 10px; vertical-align: top; padding: 2px; }
        p   { font-family: verdana; font-size: 10px; padding: 2px; }
      </STYLE>
      </HEAD>

      <BODY>'

    SET @EmailBody = @EmailBody + '
      <P>The following publications have latency above the current threshold of ' + CAST(@AcceptedLatency AS varchar(3)) + ' seconds:</P>
      <TABLE WIDTH=100%>
      <TR>
      <TH>Publication database</TH>
      <TH>Publication name</TH>
      <TH>Subscriber</TH>
      <TH>Subscriber DB</TH>
      <TH>Overall Latency</TH>
      <TH>Tracer token posted</TH>
      '

    SELECT @EmailBody = @EmailBody + '
      <TR>
      <TD>' + PublicationDatabase + '</TD>
      <TD>' + PublicationName + '</TD>
      <TD>' + Subscriber + '</TD>
      <TD>' + SubscriberDB + '</TD>
      <TD>' + CASE WHEN OverallLatency IS NULL THEN '> 2 mins (timed out)' ELSE CAST(OverallLatency AS varchar(3)) END + '</TD>
      <TD>' + CONVERT(VARCHAR(50), TracerTokenPostTime, 113) + '</TD>'
    FROM dbo.replication_latency
    WHERE IsChecked IS NULL
      AND COALESCE(OverallLatency, 1000) > @AcceptedLatency

    SELECT @EmailBody = @EmailBody + '
      </TABLE>

      </BODY>
      </HTML>'

    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'MailerProfileName',
      @recipients = @EmailRecipients,
      @subject = @EmailSubject,
      @body = @EmailBody,
      @body_format = 'HTML'

  END

END

- Lumbago

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources