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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 sp_readmail and the Loops of Infinity
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

spudhead
Starting Member

34 Posts

Posted - 06/24/2002 :  11:12:49  Show Profile  Reply with Quote
Hi,

Found the article http://www.sqlteam.com/item.asp?ItemID=401 (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

United Kingdom
846 Posts

Posted - 06/24/2002 :  14:09:29  Show Profile  Visit jasper_smith's Homepage  Reply with Quote
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

Go to Top of Page

spudhead
Starting Member

34 Posts

Posted - 06/25/2002 :  04:43:36  Show Profile  Reply with Quote
Many thanks - explanation and solution in one Works a treat, seems pretty fast, too.

Much appreciated,

Spud

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000