Email replication Notifications/alerts with xp_smtp_sendmail?

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 = 'joe@.joe123.net',
@.FROM_NAME = 'joe',
@.TO = 'joe@.joe123.net',
@.priority = 'HIGH',
@.subject = 'Replication Failure Alert',
@.message = @.s,
@.type = 'text/plain',
@.server = 'mail.joe123.net'
select RC = @.rc
GO
Thanks,
Rich
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 wrote:
> 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 = 'joe@.joe123.net',
> @.FROM_NAME = 'joe',
> @.TO = 'joe@.joe123.net',
> @.priority = 'HIGH',
> @.subject = 'Replication Failure Alert',
> @.message = @.s,
> @.type = 'text/plain',
> @.server = 'mail.joe123.net'
> select RC = @.rc
> GO
> Thanks,
> 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?
"Query Builder" wrote:

> 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 wrote:
>
|||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 .

email alerts

I was wondering if there was a nice easy way to use a simple tool to email
me when my sql2k server is having some issues? I have been looking at how to
configure SQLAgentMail and SQL Mail but do I really need a mapi client? Or
what is the easiest way to do this? Thanks for the info.
Jason
You can use xp_smtp_sendmail, just use smtp and not have to
deal with the MAPI issues. You can find the download and
information at:
http://www.sqldev.net/xp/xpsmtp.htm
In terms of alerts, you can run a job in response to alerts.
So if you had a job that sends an email notification using
xp_smtp_sendmail, you could just fire that job in response
to the alert.
-Sue
On Mon, 29 Mar 2004 12:32:38 -0600, "JasonMeyer"
<jason.meyer@.nospam.isd623.nospam.org> wrote:

>I was wondering if there was a nice easy way to use a simple tool to email
>me when my sql2k server is having some issues? I have been looking at how to
>configure SQLAgentMail and SQL Mail but do I really need a mapi client? Or
>what is the easiest way to do this? Thanks for the info.
>
>Jason
>
|||... and here are some other options:
http://www.karaszi.com/sqlserver/info_no_mapi.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message news:ur0h60p3vl3i5es10df9dtud60jp2bvpb9@.4ax.com...
> You can use xp_smtp_sendmail, just use smtp and not have to
> deal with the MAPI issues. You can find the download and
> information at:
> http://www.sqldev.net/xp/xpsmtp.htm
> In terms of alerts, you can run a job in response to alerts.
> So if you had a job that sends an email notification using
> xp_smtp_sendmail, you could just fire that job in response
> to the alert.
> -Sue
> On Mon, 29 Mar 2004 12:32:38 -0600, "JasonMeyer"
> <jason.meyer@.nospam.isd623.nospam.org> wrote:
>

Email Alert in SQL Server2000

can somebody help me to setup email alerts in SQL Server 2000 standard edition whenever, backup is successful OR failed to complete.

Thanks,

I'll assume you're doing scheduled backups via SQL Server Agent

1. Configure SQL Server to use SQL Mail (Check Books Online SQL Mail)

2. Add an operator to SQL Server Agent with the relevant email address

3. In your SQL Job, notifications tab, check the box to email, select your operator and also selec to alert whenever the job completes.

HTH!

Email Alert

I have a Windows 2003 server running SQL 2000 SP4. I want to start setting
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/d...-us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Email Alert

I have a Windows 2003 server running SQL 2000 SP4. I want to start setting
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?
Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/de...us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Email Alert

I have a Windows 2003 server running SQL 2000 SP4. I want to start setting
up email alerts and reporting on the SQL server. what is the most safe and
best way to achive this?Hi docsql,
Welcome to use MSDN Managed Newsgroup Support.
From your description, my understanding of this issue is: You want to know
how to setup a email notification for an alert in SQL Server 2000. If I
misunderstood your concern, please feel free to point it out.
In SQL Server 2000, you can define the Response to an alert and choose the
email operator to notify.
To setup a SQL Mail, you need to configure a mail profile first.
SQL Mail must have a mail profile created in the same user domain account
or context that is used to start an instance of SQL Server. You can create
the profile with a mail client, such as Microsoft Outlook, that is
installed locally on the SQL Server computer.
You can find how to configure SQL Mail in the following article.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad
_1_server_2ecs.asp
Also, following KB article may help you to set up a SQL Agent Mail.
263556 INF: How to Configure SQL Mail
http://support.microsoft.com/?id=263556
315886 INF: Common SQL Mail Problems
http://support.microsoft.com/?id=315886
321183 HOW TO: Troubleshoot Your SQL Mail Issue
http://support.microsoft.com/?id=321183
311231 INF: Frequently Asked Questions - SQL Server - SQL Mail
http://support.microsoft.com/?id=311231
Hope this will be helpful.
Sincerely yours,
Wei Lu
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.