xp_readmail: fail (mail error 0x8004010f) -SOLVED!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

xp_readmail: fail (mail error 0x8004010f) -SOLVED!

Author

Topic

PapillonUK
Starting Member

22 Posts

Posted - 2004-07-19 : 05:36:22

Have just moved a DB from one server to another.
Everything is working fine apart from one stored proc which looks for certain recognised emails and reads in attachments.

The procedure is called from a scheduled job.
I'm at SP3 (@@VERSION = 8.00.760)

The job fails with the error:

Executed as user: (Domain User Name Here).
xp_readmail: failed with mail error 0x8004010f [SQLSTATE 42000] (Error 18025) Associated statement is not prepared [SQLSTATE HY007] (Error 0) xp_readmail: failed with mail error 0x8004010f [SQLSTATE 42000] (Error 18025). The step failed.

The weird thing is...

if I log in with Query Analyser as the Domain User that the job runs as i can paste the code from the stored proc in and run it fine - it all works, it reads the mail subject lines etc. etc.

YET

If I attempt to run the stored proc using EXEC from QA - i get the same error:

Server: Msg 18025, Level 16, State 1, Line 0
xp_readmail: failed with mail error 0x8004010f

Note, SQL Agent runs under the same domain user account as it did on the old box where this procedure worked fine.

Has anyone any ideas? Thanks! Sime

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-07-19 : 05:48:35

Is the Domain Account in the local administrators group on the server ? Just a thought...

PapillonUK
Starting Member

22 Posts

Posted - 2004-07-19 : 05:53:17

Yes (it was on the old box too)

PapillonUK
Starting Member

22 Posts

Posted - 2004-07-19 : 07:48:57

Apparently error 0x8004010f means MAPI_E_NOT_FOUND - whatever that means!

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-19 : 08:10:55

You need to login as the account that SQL Server runs under and actually go into Outook. Set it as your default mail client. You can then go back into SQL Server and redo the mail settings. It "should" work for you then.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

PapillonUK
Starting Member

22 Posts

Posted - 2004-07-19 : 09:18:47

Thanks for the advice - i can't find anywhere in Outlook where you can set it as the default mail client (i'm using Outlook 2000)?

I'm beginning to think this is not the problem though as i've narrowed it down somewhat:

Here's the stored procedure (i've removed all irrelevant bits).
This displays the subject line of all unread emails in the inbox:


CREATE PROCEDURE dbo.usp_Test  AS
DECLARE @MsgID binary(255)	  
DECLARE @PrevMsgID binary(255)
DECLARE @ReceivedDateTimeStr varchar(255)
DECLARE @EmailAlias varchar(255)
DECLARE @Subject varchar(255)
DECLARE @Attachments varchar(255)
DECLARE @iRetVal INT
DECLARE @Recipients VARCHAR(255)

SET @PrevMsgID = 0
EXEC @iRetVal = master.dbo.xp_findnextmsg @msg_id=@MsgID OUT, @unread_only='true'

IF @iRetVal = 0					-- If no error (ie emails exist)...
BEGIN
	WHILE @MsgID <> @PrevMsgID	
	BEGIN
		-- Read the message, without altering it's 'read' status...
		EXEC	master.dbo.xp_readmail
			@msg_id=@MsgID, 
			@Date_received = @ReceivedDateTimeStr OUT,
			@peek = 'true',
			@originator = @EmailAlias OUT,
			@Subject = @Subject OUT, 
			@attachments = @Attachments OUT

		SELECT @subject

		SET @PrevMsgID = @MsgID
		EXEC master.dbo.xp_findnextmsg @msg_id=@MsgID OUT, @unread_only='true'
	END
END

When I comment out the CREATE PROCEDURE LINE and run it in QA it works fine.
But if i compile it as a stored proc then run it from QA using "EXEC usp_test" the loop never ends and when i break i can see it has actually output the subject lines of all my emails again and again - plus i'm getting loads of errors saying:

Server: Msg 18025, Level 16, State 1, Line 0
xp_readmail: failed with mail error 0x8004010f

Think I need to look at why the loop should never end when i execute it as a SP but ends fine if the code is executed directly in QA (!!!!???)

PapillonUK
Starting Member

22 Posts

Posted - 2004-07-19 : 10:25:36

SORTED!!!!

My old SQL server was at SP1 the new one is at SP3.

Microsoft have surreptitiously altered the xp_findnextmsg return value.

Originally it would continualy return the same @MsgID when it got to the end of the emails, now it returns NULL (which is better).

This explains why the loop never ended when run as a SP but did end when run in QA.

Due to the default settings in QA the loop ended when xp_findnextmsg returned NULL twice in a row
ie. when NULL=NULL

Whereas, when run as a SP NULL handling is more strict and NULL=NULL would not return true!

My code can now be simplified to:


CREATE PROCEDURE dbo.usp_Test  AS
DECLARE @MsgID binary(255)	  
DECLARE @ReceivedDateTimeStr varchar(255)
DECLARE @EmailAlias varchar(255)
DECLARE @Subject varchar(255)
DECLARE @Attachments varchar(255)
DECLARE @iRetVal INT
DECLARE @Recipients VARCHAR(255)

SET @PrevMsgID = 0
EXEC @iRetVal = master.dbo.xp_findnextmsg @msg_id=@MsgID OUT, @unread_only='true'

IF @iRetVal = 0					-- If no error (ie emails exist)...
BEGIN
	WHILE @MsgID IS NOT NULL
	BEGIN
		-- Read the message, without altering it's 'read' status...
		EXEC	master.dbo.xp_readmail
			@msg_id=@MsgID, 
			@Date_received = @ReceivedDateTimeStr OUT,
			@peek = 'true',
			@originator = @EmailAlias OUT,
			@Subject = @Subject OUT, 
			@attachments = @Attachments OUT

		SELECT @subject

		EXEC master.dbo.xp_findnextmsg @msg_id=@MsgID OUT, @unread_only='true'
	END
END

Thanks to everyone's that posted help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources