sp_readmail and the Loops of Infinity

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 Administration (2000)

sp_readmail and the Loops of Infinity

Author

Topic

spudhead
Starting Member

34 Posts

Posted - 2002-06-24 : 11:12:49

Hi,

Found the article [url]http://www.sqlteam.com/item.asp?ItemID=401[/url] (regarding reading mailboxes with SQLMail) really useful, nice one :)

However, using the code discussed on that page (detail below) I seem to be getting Query Analyser into an infinite loop, and there's only 5 or 6 messages in the mailbox I'm accessing. It seems to be returning the most recent message continually - and I'm afraid my patchy SQL isn't really up to debugging.

Does anyone have a decent explaination for this behaviour that they could put into nice, short, friendly words for me? Or a suggestion as to how I could modify the query so it gave me a little more info about what's happening?

Thanks very much, like.

Spud.


DECLARE @hMessage varchar(255), @MessageText varchar(1000)

EXEC xp_findnextmsg @msg_id=@hMessage OUT
WHILE @hMessage IS NOT NULL
BEGIN
exec xp_readmail @msg_id=@hMessage, @message=@MessageText OUT
SELECT 'You''ve got mail! Here it is: ' + @MessageText 
EXEC xp_findnextmsg @msg_id=@hMessage OUT
END

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-06-24 : 14:09:29

Fixed code below.
The default behaviour of xp_findnextmsg is to find all mail whether its been marked read or not thus you will just get an infinite loop as @hMessage will never be NULL. It also seems that a NULL OUTPUT parameter doesn't seem to set @hMessage to NULL so explicitly setting it to NULL seems to work. The below code works for me. Bear in mind that it is a fairly slow process - I only had 2 mail messages I put in the inbox and it still took 16 seconds on my home PC

HTH
Jasper Smith

Declare @hMessage varchar(255),@msg_id varchar(255)
Declare @MessageText varchar(8000),@message varchar(8000)

EXEC xp_findnextmsg @unread_only='true',@msg_id=@hMessage OUT

WHILE @hMessage IS NOT NULL
BEGIN
exec xp_readmail @msg_id=@hMessage, @message=@MessageText OUT

SELECT 'You''ve got mail! Here it is: ' + @MessageText

SET @hMessage = NULL

EXEC xp_findnextmsg @unread_only='true',@msg_id=@hMessage OUT
END

spudhead
Starting Member

34 Posts

Posted - 2002-06-25 : 04:43:36

Many thanks - explanation and solution in one

Works a treat, seems pretty fast, too.

Much appreciated,

Spud

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources