Please run these SQL changes (they can be run in the Admin->Run SQL Query area).
If you have v1.9.6 or greater, you need to run the following SQL:
ALTER PROCEDURE [{databaseOwner}].[{objectQualifier}mail_list]
(
@ProcessID int,
@UTCTIMESTAMP datetime
)
AS
BEGIN
BEGIN TRANSACTION TRANSUPDATEMAIL
UPDATE [{databaseOwner}].[{objectQualifier}Mail]
SET
ProcessID = NULL
WHERE
ProcessID IS NOT NULL AND SendAttempt > @UTCTIMESTAMP
UPDATE [{databaseOwner}].[{objectQualifier}Mail]
SET
SendTries = SendTries + 1,
SendAttempt = DATEADD(n,5,@UTCTIMESTAMP),
ProcessID = @ProcessID
WHERE
MailID IN (SELECT TOP 10 MailID FROM [{databaseOwner}].[{objectQualifier}Mail] WHERE SendAttempt < @UTCTIMESTAMP OR SendAttempt IS NULL ORDER BY SendAttempt, Created)
COMMIT TRANSACTION TRANSUPDATEMAIL
-- now select all mail reserved for this process...
SELECT TOP 10 * FROM [{databaseOwner}].[{objectQualifier}Mail] WHERE ProcessID = @ProcessID ORDER BY SendAttempt, Created desc
END
For v1.9.5.x, please use the following SQL:
ALTER PROCEDURE [{databaseOwner}].[{objectQualifier}mail_list]
(
@ProcessID int
)
AS
BEGIN
DECLARE @UTCTIMESTAMP datetime
SET @UTCTIMESTAMP = GETUTCDATE()
BEGIN TRANSACTION TRANSUPDATEMAIL
UPDATE [{databaseOwner}].[{objectQualifier}Mail]
SET
ProcessID = NULL
WHERE
ProcessID IS NOT NULL AND SendAttempt > @UTCTIMESTAMP
UPDATE [{databaseOwner}].[{objectQualifier}Mail]
SET
SendTries = SendTries + 1,
SendAttempt = DATEADD(n,5,@UTCTIMESTAMP),
ProcessID = @ProcessID
WHERE
MailID IN (SELECT TOP 10 MailID FROM [{databaseOwner}].[{objectQualifier}Mail] WHERE SendAttempt < @UTCTIMESTAMP OR SendAttempt IS NULL ORDER BY SendAttempt, Created)
COMMIT TRANSACTION TRANSUPDATEMAIL
-- now select all mail reserved for this process...
SELECT TOP 10 * FROM [{databaseOwner}].[{objectQualifier}Mail] WHERE ProcessID = @ProcessID ORDER BY SendAttempt, Created desc
END
Jaben wrote: