xp_sendmail: failed with mail error 0x80004005

[mutley1]

Hi all,

Running on SQL 2000 SP3a. I have a server configured to email alerts etc. and they all work fine (test mail to operator etc.). I have a job that calls a stored procedure, and all it does is check the time in a lockfile against the system time and will send an email to me if the difference is greater than 15 minutes. This is working fine on our test servers, but live is playing up and gives the following error if it needs to email me (the sp works fine in Analyser and by the job if there is nothing to send. However, if the job falls over with the below message, the Alerter e-mails me (and it comes through!!).

xp_sendmail: failed with mail error 0x80004005 [SQLSTATE 42000] (Error 18025) Associated statement is not prepared [SQLSTATE HY007] (Error 0). The step failed.

Also falls over in QA if it needs to mail me). It's a bit strange because the test server is exactly the same and has no problems. Any help greatly appreciated.

The stored procedure can be seen below.....

CREATE PROCEDURE az_check_despatched_process
AS


DECLARE @mins_runtime int
DECLARE @message VarChar(255)

SET @mins_runtime = (select (DATEDIFF(mi, database..table.start_time, getdate()))
FROM database..table
WHERE database..table.lock = 'despatched')

/*select @mins_runtime*/

IF (@mins_runtime < 15 or @mins_runtime is null) goto end_prog

Select @message = (Select 'Process DESPATCHED has currently been running for '+ CONVERT (char(4),@mins_runtime) + ' minutes

This is longer than the recommended 15 minute threshold. Please investigate.')

EXEC master..xp_sendmail 'jbloggs@mycompany.com',
@message,
@subject = 'WARNING - BCS Process DESPATCHED. Long Runtime.'

end_prog:


GO

 

[spencer2]

Did you ever figure this one out?

I am experiencing the same error, but for possibly a different reason -- I have a sql agent job in SQL Server 2000 SP2 that runs every 10 minutes to send out any e-mail alerts I have stored in a database staging table. The job was running fine until a few days ago. The only thing that changed (as far as I can tell) is the exchange mailbox sql mail uses was just moved from Exchange 5.5 to Exchange 2003. Ever since that happened, the job has been failing. I see a message like this in the job history log:

xp_sendmail: failed with mail error 0x80004005 [SQLSTATE 42000] (Error 18025) Associated statement is not prepared [SQLSTATE HY007] (Error 0) xp_sendmail: failed with mail error 0x80004005 [SQLSTATE 42000] (Error 18025). The step failed.

I found this Microsoft Article about the error, which applies to upgrading the mailbox from Exchange 5.5 to Exchange 2000 (and I assume it applies as well if you upgrade from Exchange 5.5 to exchange 2003):

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q293422

Perhaps the mail account you use to send sql mail is configured with "send as" rights (see the article) while the production sql mail account is not?

 

[mutley1]

Hi Spencer,

I never did figure it out, and we are on Exchange 5.5 so it seemed really bizarre. Firstly, I believe it is working now and the only thing that changed was a fe stops and starts on the SQL Agent, but secondly I did find a workaround. Instead of the xp_sendmail (as i only used it to tell me how long the job had been running) I just used a raiserror (created a user defined message for myself) and set that to mail me on the notifications in alerts. OK I cant get the exact runtime, but I was only interested in if the job hung - the time was just a 'nice to have'!

Depending on the type of alert you have in your staging table (i.e. don't need to extract any data to attach to them) you might be able to get away with the same thing - create the user defined in alerts and set the notification to mail you then use the criteria to do a raiserror instead of xp_sendmail. Good luck!!