Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
I haven't set this up on 2000 before, but on 2005+ you have to configure SQL Agent to use the database mail profile and then restart the Agent service. Did you do that? –  Jon Seigel May 22 '12 at 21:08
    
@JonSeigel -I thought I had, but when I go to check it in SSMS, the option for "Enable Mail Profile" is greyed out, so no profile is selected. How can I get this option active? –  MAW74656 May 22 '12 at 21:12
    
Aside from configuring Database Mail, I have no idea. I don't have access to a SQL 2000 instance to test with. Sorry. –  Jon Seigel May 22 '12 at 21:27
1  
On 2000 don't you have to have an actual client installed (like Outlook) and configure it using the Mail icon in the Control Panel (which might not even be an option depending on the OS)? –  Aaron Bertrand May 22 '12 at 21:52
1  
Outlook is installed on your SQL Server? OK, so follow these steps: support.microsoft.com/kb/263556 –  Aaron Bertrand May 23 '12 at 14:58

2 Answers 2

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.

share|improve this answer
    
Example of using tokens? –  MAW74656 May 23 '12 at 14:47
    
    
I'm really lost on how to construct a stored proc for this. –  MAW74656 May 23 '12 at 14:52
    
Are you suggesting something better than a static stored proc which says "Job name abc failed at 10am on 5/23/2012"? –  MAW74656 May 23 '12 at 14:58
1  
@MAW74656 well you would pass the job name to the stored procedure, but essentially, that's what I'm suggesting. But hey, if you want to go ahead and configure SQL Mail, be my guest. support.microsoft.com/kb/263556 –  Aaron Bertrand May 23 '12 at 14:59

You need to ensure that the mail profile is also enabled for SQLAgent.

Right-click SQLAgent on the instance in question. Ensure that there is a mail profile selected in the "Mail Session" dropdown box.

Operator notifications are driven by SQLAgent, not the the database engine.

Old-school dbmail wasn't really that bad, you just had to know how to set it up.

share|improve this answer
    
In SSMS, I can't, the option is greyed out. –  MAW74656 May 23 '12 at 14:45
1  
Do you have Enterprise Manager installed on the server itself? If so, you can login to the server and run EM locally and then set it. –  m4rty May 23 '12 at 15:24
1  
Can't edit my above correctly.. If the option is greyd-out, your SQLAgent service may be running under a different service account than the database engine. If so, you'll need to setup a matching mail profile for that account as well. –  m4rty May 23 '12 at 15:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.