July 23, 2009 at 6:01 am
In order to receive email messages of a nightly backup job that has run successfully, does anyone know of a way to accomplish this without having to install Outlook 2000 on a SQL Server 2000 machine?
Thank you.
Rog
July 23, 2009 at 6:59 am
There is a stored procedure called xp_sendmail (see BOL) that I use all the time. Wrap it into a stored proc and then send on success. Personally, I would send on failure only. If you don't get a message, it worked, and no pagers/Blackberry sounding off in the middle of the night telling me the job completed successfully - just my 2 cents.
-- You can't be late until you show up.
July 23, 2009 at 7:29 am
Thanks for the head's up. Anyway you can send me an example of what you've done for a backup failure message stored proc?
Rog
July 23, 2009 at 7:40 am
I really don't use it for success or failures but something like this should work:
CREATE PROCEDURE [dbo].[On_Success]
EXEC MASTER.dbo.xp_sendmail
@recipients = rogerabram@gmail.com
@subject = 'Backup was successful',
@message = 'Backup was successful'
go
Then in the scheduled job, add another step to "exec On_Success" which should be executed at the "on success action" (on the advanced tab) of the backup step, otherwise whatever would be your next step on failure
-- You can't be late until you show up.
July 23, 2009 at 8:10 am
I created the procedure and just try to run it and get the following message:
Server: Msg 17985, Level 16, State 1, Line 0
xp_sendmail: Procedure expects parameter @user, which was not supplied.
From looking in sqlservercentral.com and other sites, it appears I still need to create some sort of email profile from Outlook 2000.
Create Procedure On_Success
as
EXEC master.dbo.xp_sendmail
@recipients=N'rogerabram@gmail',
@message=N'Backup was successful',
@subject=N'Backup was successful' ;
GO
July 23, 2009 at 9:06 am
Go to BOL, check out xp_sendmail.
Blurb from BOL:
Remarks
The SQL Mail session must be started prior to executing xp_sendmail. Sessions can be started either automatically or with xp_startmail. For more information about setting up a SQL Mail session automatically, see Configuring Mail Profiles. One SQL Mail session supports all users on the SQL Server, but only one user at a time can send a message. Other users sending mail messages automatically wait their turns until the first user's message is sent.
-- You can't be late until you show up.
July 23, 2009 at 9:19 am
In 2000, if you use the built in mail, you have to use Outlook, or a MAPI complaint system. You can get some SMTP senders, like XPSMTP (http://www.sqldev.net/xp/xpsmtp.htm), but that's still an install. Honestly I've never seen an issue installing Outlook in large and small companies. It's understood, it's simple, and easier to explain than an XP addition to me.
I'd also note that I wouldn't just send a message on job failure. I'd insert a value into a table. I'd then have something else to check if mail doesn't work. You could even have another machine look for values in that table from last night and send a 2nd reminder for someone to deal with it if you need that level of fault tolerance.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply