Email replication Notifications/alerts with xp_smtp_sendmail?

[Rich]

Hello,

I installed the xpsmtp80.dll for sql server 2000 so that I can send emails
from sql server without using sqlmail. xpsmtp80.dll uses the
xp_smtp_sendmail extended stored procedure. This works find from query
analyzer and from stored procedures called from dts jobs... But how to
implement/use xp_smtp_sendmail with replication notifications/alerts?

I looked at the properties of the distribution agent and in the
notifications tab there is an option to email the operator when the job
fails. But this uses sqlmail, which I want to circumvent because I don't
want to use outlook on the server machine. How can I invoke an sp like this
one when a replication job fails?

CREATE PROCEDURE stp_testEmailproc

AS
declare @rc int, @s varchar(100)
SET @s = 'Job failed on ' + cast(getdate() AS varchar(30))
exec @rc = master.dbo.xp_smtp_sendmail
@FROM = 'j...@joe123.net',
@FROM_NAME = 'joe',
@TO = 'j...@joe123.net',

@priority = 'HIGH',
@subject = 'Replication Failure Alert',
@message = @s,
@type = 'text/plain',

@server = 'mail.joe123.net'
select RC = @rc
GO

Thanks,
Rich

[Query Builder]

Hi Rich,

I work with a lot of replication databases. One of the concerns we have
is to instantly notify us when a replication fails as we use huge DBs
to replicate over the general Internet and we cannot afford to do a
resynch as it will take days(even weeks).

So what we have done is develop a SQL Job that only sends out an email
taking an optional parameter.

On the Replication alerts, I have configured all the alerts to call
this job. This helps us inform there is an issue but we still have to
log in to see what failed. Also you can modify the distribution SQL
Jobs to call this job if the step fails.. That way you can use the
optional parameter to pass the step detail name to pin point which
agent failed... Hope this helps..

[Rich]

Well, at least there is a way to do this. Right now I just need a way to get
notified by email when replication fails. How do you configure Replication
alerts to call a job?

[Paul Ibison]

Rich,
have a look at the "Replication: agent failure" alert. On the response tab
there is an "Execute job" checkbox....
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .