Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: "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:

    Code:
    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=@MessageText 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)+@MessageText
    				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)+@MessageText
    				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.

  2. #2
    Join Date
    Jan 2002
    Posts
    189
    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?

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    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?

  4. #4
    Join Date
    Jan 2002
    Posts
    189
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •