Please help! Job failing (mail failure)

Question

Post reply

Please help! Job failing (mail failure)

the__t

Hall of Fame

Points: 3260
More actions
April 2, 2008 at 7:20 am

#120904

Please help. I have a job which is executing a stored procedure. The job keeps failing every hour with the following message:
"SQL Server blocked access to procedure 'sys.xp_sendmail' of component 'SQL Mail XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'SQL Mail XPs' by using sp_configure. For more information about enabling 'SQL Mail XPs', see "Surface Area Configuration" in SQL Server Books Online."
I know how to modify that setting - that's not my problem. We have that turned off. We are using Database Mail.
I've tested database mail - that works fine.
I've tested the exact statement in the stored procedure which sends the mail (exec msdb..sp_send_dbmail) - that works fine.
I've scanned the stored procedure for any mention of "xp_sendmail" being called - there are no references to this SP that I have found, in this stored proc, or any stored proc that is called from another stored proc involved in this job/procedure. (Unless it's very obscured, I'm not finding it)
"Got no time for the jibba jabba!"
-B.A. Baracus

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Animal Magic SSChampion Points: 13964 More actions · Answer 1

ignore what i first wrote! i didnt read the whole post :ermm:

Have you got a notification on the job outcome? Failure/Success etc?

the__t Hall of Fame Points: 3260 More actions · Answer 2

Yes, I know, that's why I'm confused... I don't see any reference to xp_sendmail

From my original post:

I've tested the exact statement in the stored procedure which sends the mail (exec msdb..sp_send_dbmail) - that works fine.

"Got no time for the jibba jabba!"
-B.A. Baracus

the__t Hall of Fame Points: 3260 More actions · Answer 3

Yes, notify the default operator on failure

"Got no time for the jibba jabba!"
-B.A. Baracus

Animal Magic SSChampion Points: 13964 More actions · Answer 4

ok. apologies for my first reply btw.

Do any jobs that notify the operator work? It could be that the agent mail has not been setup to use the database mail profile.

the__t Hall of Fame Points: 3260 More actions · Answer 5

No worries.

Unfortunately, all of the jobs only notify on failure, and at this point, I can only see two jobs (still in the history) that have failed, and both are very similar (one is checking drivespace, one is checking data and log space). Both are failing for this same reason as of 3 days ago (they worked fine before that).

But I did check the agent properties, and it's configured for Database mail using the default profile.

I also checked the settings for Database Mail, and everything seems to be in order.

"Got no time for the jibba jabba!"
-B.A. Baracus

Animal Magic SSChampion Points: 13964 More actions · Answer 6

hmm ok, thats rather odd. Is there any more info in the logs that are not in the failure message your getting in the job history.

And just to clarify, the failure message used to email out ok on the same server/same config etc until 3 days ago?

the__t Hall of Fame Points: 3260 More actions · Answer 7

I can't say for certain what might have changed 2-3 days ago, but I'm unsure of whether the failure message worked fine before that, because the job wasn't failing before that. 🙂 Go figure...

I don't see anything else in any of the logs, including the SQLAGENT.OUT

"Got no time for the jibba jabba!"
-B.A. Baracus

Steve Jones - SSC Editor SSC Guru Points: 736468 More actions · Answer 8

Very strange. Have you tried a Profiler trace to see what's happening here? Maybe limit to the SQLAgent user to see if that's the one calling this?

Animal Magic SSChampion Points: 13964 More actions · Answer 9

ok, to test the mail try this...

exec sp_addmessage @msgnum = 50001 ,@severity = 11, @msgtext = N'Test error'

,@with_log = 'True'

GO

USE [msdb]

GO

EXEC msdb.dbo.sp_add_alert @name=N'Test Alert',

@message_id=50001,

@severity=0,

@enabled=1,

@delay_between_responses=0,

@include_event_description_in=1,

@category_name=N'[Uncategorized]',

@job_id=N'00000000-0000-0000-0000-000000000000'

GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'Test Alert', @operator_name=N'XXXXX', @notification_method = 1

GO

Replace the XXXXX in operator_name with a valid name of your operator

then just run raiserror(50001,16,1) and this should raise your alert and email you if your mail profile it working correctly.

the__t Hall of Fame Points: 3260 More actions · Answer 10

Ok, that worked. Email received:

DATE/TIME:4/2/2008 10:25:19 AM

DESCRIPTION:Error: 50001 Severity: 16 State: 1 Test error

COMMENT:(None)

JOB RUN:(None)

"Got no time for the jibba jabba!"
-B.A. Baracus

the__t Hall of Fame Points: 3260 More actions · Answer 11

Steve Jones - Editor (4/2/2008)
Very strange. Have you tried a Profiler trace to see what's happening here? Maybe limit to the SQLAgent user to see if that's the one calling this?

Note my signature... 🙂

My training hasn't gotten to the point of running any traces yet.

"Got no time for the jibba jabba!"
-B.A. Baracus

the__t Hall of Fame Points: 3260 More actions · Answer 12

This is resolved. Apparently the SP on the server (which is encrypted) was using an old version that did utilize xp_sendmail. The SP source, prior to encryption, is located on a share drive, and when I was viewing that, I was viewing a new version without the xp_sendmail logic but it had not been implemented on the server yet.

So, no mystery here, just unfortunate timing between what I was looking at and what another dba was doing. 🙂

"Got no time for the jibba jabba!"
-B.A. Baracus