Hi,

Right, this is a bit of a continuation of this topic, but I think it warrants a new thread.

One scheduled job runs two stored procs every three minutes. Both stored procs run identical email-reading functions in different databases.

I'm getting an error "Associated statement is not prepared", and I think it's because I'm trying to run two stored procs that do the same thing at the same time: it's trying to allocate resources to running the second job step that the first one is still using.

So my question is kinda twofold: firstly, has anyone had any experience of this type of error/problem? Would I get the error message I'm getting for (roughly speaking) the reasons above?

Secondly, I guess the underlying root of my problem is that the stored procedures take too long - they loop through a mailbox contents, reading and - if necessary - processing email. This is, I understand, a notoriously time-consuming task. Is there any way I can speed the stored proc's up?

In case anyone's interested, the stored proc that gets run in both databases is:

Code:
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=SUBSTRING(@MessageSubject, (CHARINDEX(':', @MessageSubject)+1), CHARINDEX('(', @MessageSubject))
		-- 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' AND email<>@Origin
		open em_cursor1
		fetch next from em_cursor1
		into @em1
		while @@FETCH_STATUS=0
		begin				--CREATE EMAIL
			select @sbj1='New HIS Forum Post: '+@mySubject+' (ThreadID='+cast(@existingID as varchar)+')'
			select @bdy1='User '+@Origin+' has replied to a thread on the HIS 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' AND email<>@Origin
		open em_cursor2
		fetch next from em_cursor2
		into @em2
		while @@FETCH_STATUS=0
		begin
			select @sbj2='New HIS Forum Post:'+@MessageSubject+' (ThreadID='+cast(@newID as varchar)+')'
			select @bdy2='User '+@Origin+' has started a new thread on the HIS 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
Thanks for any help.