"complex" SQL and bulk mailing

Well, you guys probably don't think this is very complex SQL :) , but it's taken me ages and it's STILL not working right. I submit the following:

CREATE PROCEDURE forum_readmail AS
Declare @.hMessage varchar(255),@.msg_id varchar(255),@.MessageText varchar(8000),@.message varchar(8000),@.MessageSubject varchar(8000),@.subject varchar(8000),@.Origin varchar (8000),@.originator_address varchar(8000)
EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN
exec master.dbo.xp_readmail @.peek='true',@.msg_id=@.hMessage,@.message=@.MessageTe xt OUT,@.subject=@.MessageSubject OUT,@.originator_address=@.Origin OUT
IF ((SELECT COUNT(*) FROM forum_users WHERE email=@.Origin)=1)
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF REPLYING TO EXISTING THREAD
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500), @.usr1 int, @.mySubject varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
SELECT @.mySubject=subject FROM forum_threads WHERE id=@.existingID
-- GET USERS ID FROM EMAIL
declare user_cursor1 cursor for
SELECT id from forum_users WHERE email=@.Origin
open user_cursor1
fetch next from user_cursor1
into @.usr1
while @.@.FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@.MessageText, CHAR(13)+CHAR(10), '<br>'), @.existingID, @.usr1)
fetch next from user_cursor1
into @.usr1
end
close user_cursor1
deallocate user_cursor1

--GET EMAIL LIST
declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1
while @.@.FETCH_STATUS=0
begin --CREATE EMAIL
select @.sbj1=@.Origin+': '+@.mySubject+' (ThreadID='+cast(@.existingID as varchar)+')'
select @.bdy1='User '+@.Origin+' has replied to a thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messa geText
exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END
ELSE -- IF NEW THREAD
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500), @.usr2 int

-- INSERT INTO FORUM_THREADS
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY

-- GET USERS ID FROM EMAIL
declare user_cursor2 cursor for
SELECT id from forum_users WHERE email=@.Origin
open user_cursor2
fetch next from user_cursor2
into @.usr2
while @.@.FETCH_STATUS=0
begin -- INSERT INTO FORUM_POSTS
INSERT INTO forum_posts (body, thread_id, user_id) VALUES (REPLACE(@.MessageText, CHAR(13)+CHAR(10), '<br>'), @.newID, @.usr2)
fetch next from user_cursor2
into @.usr2
end
close user_cursor2
deallocate user_cursor2
--GET EMAIL LIST
declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2
while @.@.FETCH_STATUS=0
begin
select @.sbj2=@.Origin+': '+@.MessageSubject+' (ThreadID='+cast(@.newID as varchar)+')'
select @.bdy2='User '+@.Origin+' has started a new thread on the test forum. The post reads:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messa geText
exec master.dbo.xp_sendmail
@.recipients=@.em2,
@.message=@.bdy2,
@.subject=@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END
END
SET @.hMessage = NULL
EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
END
GO

This runs as a scheduled job every 10 minutes. There are about 600 users in the 'forum_users' table - which our experiments indicate it should take about 3 minutes to email.

It seems to be having problems, though - it sits continually in an "Executing job" state, even sometimes when there aren't any emails in the mailbox to read. And sometimes just fails completely - won't read anything.

What could be causing this? Where should I start? Is the code as clean and swift as it can be? Should I be especially vigilant of invalid email addresses? Might we be having network problems? Should I not even be thinking of using SQLMail to deal with large (ie: absolute maximum of about 1500 users) volumes of email both to read and send out?

Help. My fingers are bleeding and my boss is going to beat me.Right, we've done some more testing. It seems that setting it to read all emails (not just the unread ones) and not to "peek" at them (ie: leave them as unread) stops it stitting there in an "executing" state. But there are still problems.

Firstly, it fails every other time. It's like this:
I delete every message in the mailbox
I send it a single email
I start the job - it immediately switches to "Failed".
I start the job again - it spends a few seconds on "Executing" (there are about 100 email addresses to send emails to) and finally switches to "Successful".

It only seems to do this when there's only new messages in the mailbox. I don't understand why.

Secondly - and this is the thing we're hoping to actually test - I have concerns about the volumes that SQLmail can actually cope with. It seems to be able to generate email at about 1000 per minute, but if I've got 1000 users in the database and there's 10 new forum posts for it to process, it's gotta send out 10,000 emails.

Is this a ridiculous thing to expect SQL mail to be able to do?|||Oh, there's one more thing. We've also got a problem with Exchange returning delivery failure notification emails to the mailbox that SQL mail is reading - which can't be helping. Now, we've tried setting Exchange to stop sending the blasted things, but to no avail. So I changed the SP above to include (immediately after the "exec master.dbo.xp_readmail" line), the following:

IF (CHARINDEX('Undeliverable', @.MessageSubject)>0)
BEGIN
exec master.dbo.xp_deletemail @.msg_id=@.hMessage
END

but it very blatantly isn't deleting anything with "Undeliverable" in the subject. Can anyone think why?|||OK, update:

I've got round the error message it was generating, but it's still ignoring this 'if' statement, and I've NO idea why. I've tried various checks, including:
IF (@.MessageSubject LIKE '%Undeliverable%')
and
IF (@.Origin LIKE '%System%')
(where @.Origin is the sender of the email; 'System Administrator')

It's just not deleting the email, and it SHOULD. Does anyone know why?