Sql-server – Trouble with Alerts and Notifications on SQL Server 2000

sql serversql-server-2000sql-server-agent

I'm trying to set up alerts and notifications in SQL Server 2000, but they are not working. I'm testing them for Agent scheduled jobs, and I get a note saying:

NOTE: Failed to notify '[operatorname]' via email.

Then in the Current Error log, I see an entry like:

[264] An attempt was made to send an email when no email session has been established

xp_sendmail works fine, and I'm actually using it in these agent jobs. The notifications are supposed to tell me the procedure completed successfully. I'm only asking for completion notification to get this working, then I'll change it to failure notifications.

Anyone know why this is happening and how to correct? I'd really like notifications to work.

Best Answer

For SQL Server 2000, I quite honestly wouldn't bother trying to get operators to work. SQL Mail is a royal PITA and requires Outlook or a similar mail client to be installed on the server. I would rather just setup each job to have a step called "mail on failure", which uses a token to identify the job, and then calls a stored procedure. You would only reach that step in the workflow if you set a step to go to that step on failure. Otherwise the step before it simply quits the job with success.

A couple of alternatives:

(1) I used xp_smtp_sendmail with great success when I was still straddled with SQL Server 2000. The challenge is it used to be available on sqldev.net, but Gert seems to have abandoned that site. So you may have to search around to find a copy of the DLL. It was written by a Microsoft employee but the standard "download from a trusted source only" disclaimer applies.

(2) Use CDO. The same ick factor as SQL Mail but no dependency on Outlook / MAPI. See this KB article for some info, but here is an example that sets up a stored procedure to send job mail failures and then how to include a job token in a job step to call that stored procedure.

First, a generic procedure to send the e-mail (you may want to re-use this for more than just job failures):

CREATE PROCEDURE [dbo].[sp_send_cdosysmail]
   @From       VARCHAR(320)   = 'default_admin@yourcompany.com',
   @To         VARCHAR(320)   = 'default_admin@yourcompany.com',
   @Subject    NVARCHAR(4000) = N'',
   @Body       NVARCHAR(4000) = N'',
   @SMTPServer VARCHAR(2000)  = '127.0.0.1'
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @obj INT;

  EXEC sp_OACreate 'CDO.Message', @obj OUTPUT;
  EXEC sp_OASetProperty           @obj,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value',
    '2';
  EXEC sp_OASetProperty           @obj,
    'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value',
    @SMTPServer;
  EXEC sp_OAMethod                @obj,
    'Configuration.Fields.Update',
    NULL;
  EXEC sp_OASetProperty           @obj, 'To',       @To;
  EXEC sp_OASetProperty           @obj, 'From',     @From;
  EXEC sp_OASetProperty           @obj, 'Subject',  @Subject;
  EXEC sp_OASetProperty           @obj, 'TextBody', @Body;
  EXEC sp_OAMethod                @obj, 'Send',     NULL;
  EXEC sp_OADestroy               @obj;
END
GO

Next, a procedure to handle the e-mail specifically for job failures:

CREATE PROCEDURE dbo.JobAlertSender
   @To      VARCHAR(320) = 'default_admin@yourcompany.com',
   @job_id  VARCHAR(36)
AS
BEGIN
   SET NOCOUNT ON;

   DECLARE @Subject NVARCHAR(4000);
   SELECT @Subject = N'Job failed: ' + name
       FROM msdb.dbo.sysjobs
       WHERE job_id = @job_id;

   DECLARE @Body NVARCHAR(4000);
   SET @Body = CONVERT(CHAR(10), GETDATE(), 120) 
       + ' ' + CONVERT(CHAR(5),  GETDATE(), 108);

   EXEC dbo.sp_send_cdosysmail
        @From    = 'default_admin@yourcompany.com',
        @To      = @To,
        @Subject = @Subject,
        @Body    = @Body;
END
GO

Now you can add a step to your job that calls this procedure. You would only hit this step if a previous step failed. The step would have code like this (note that you may want to override the default "To" person depending on the job):

EXEC master.dbo.JobAlertSender
   -- @To = 'override@yourcompany.com',
   @job_id = '(JOBID)';

Here is how the job step would look:

enter image description here

And here is how you would set up previous steps, either using "go to the next step" or picking the failure step explicitly from the drop-down:

enter image description here

If you find xp_smtp_sendmail, you'd just call a slightly different procedure from dbo.JobAlertSender.

If you don't have a valid SMTP server, you'll need to install an SMTP server locally. For help with that you'll want to hit up SuperUser or ServerFault. If you need something to test locally in the meantime, nothing could be simpler than smtp4dev. You won't get the e-mails in your inbox, but you can verify the contents, headers, etc until you do have a proper SMTP server in place.

Google also told me I wrote this article in 2002 which may contain some useful information.

Related Question