USP_EMAILJOB_UPDATESTATUSFROMSERVICES
Updates status of CMS email jobs based on the status from email services. Returns the IDs of the EmailJobs that have errored or are completed.
Definition
Copy
CREATE procedure dbo.USP_EMAILJOB_UPDATESTATUSFROMSERVICES
as
begin
declare @NOW datetime = GETUTCDATE()
--------------------------------------------------------------
--Exceptions
declare @ERRORJOBSTOUPDATE table (ID int, WSDateLastAccessed datetime)
insert into @ERRORJOBSTOUPDATE
select EJ.ID, COALESCE(SERVERMODIFIEDDATE, CLIENTPROCESSENDDATE, @NOW)
from dbo.EMAILJOB EJ
inner join dbo.EMAILSTATUSGENERALPURPOSE S on S.ID = EJ.STATUSID
where S.[STATUS] in (4,16,17) and EJ.[STATUS] <> 14
insert into @ERRORJOBSTOUPDATE
select EJ.ID, COALESCE(SERVERMODIFIEDDATE, CLIENTPROCESSENDDATE, @NOW)
from dbo.EMAILJOB EJ
inner join dbo.EMAILSTATUSTRANSACTIONAL S on S.ID = EJ.STATUSID
where S.[STATUS] in (4,16,17) and EJ.[STATUS] <> 14
update dbo.EMAILJOB set [Status] = 14, [WSDateLastAccessed] = J.WSDateLastAccessed
from dbo.EMAILJOB EJ
inner join @ERRORJOBSTOUPDATE J on J.ID = EJ.ID
insert into dbo.EmailJobStatus (EmailID, EmailJobID, CreateDate, UpdateDate, Status, ErrorText)
select EJ.EmailID, EJ.ID, J.WSDateLastAccessed, J.WSDateLastAccessed, 'Exception on server', ESGP.STATUSMESSAGE.value('data(/Errors/Exception[1]/@Message)[1]','nvarchar(MAX)')
from dbo.EMAILJOB EJ
inner join @ERRORJOBSTOUPDATE J on J.ID = EJ.ID
inner join dbo.EMAILSTATUSGENERALPURPOSE ESGP on ESGP.ID = EJ.STATUSID
insert into dbo.EmailJobStatus (EmailID, EmailJobID, CreateDate, UpdateDate, Status, ErrorText)
select EJ.EmailID, EJ.ID, J.WSDateLastAccessed, J.WSDateLastAccessed, 'Exception on server', ESTP.STATUSMESSAGE.value('data(/Errors/Exception[1]/@Message)[1]','nvarchar(MAX)')
from dbo.EMAILJOB EJ
inner join @ERRORJOBSTOUPDATE J on J.ID = EJ.ID
inner join dbo.EMAILSTATUSTRANSACTIONAL ESTP on ESTP.ID = EJ.STATUSID
--------------------------------------------------------------
--Uploading
declare @UPLOADINGJOBSTOUPDATE table (ID int, WSDateLastAccessed datetime)
insert into @UPLOADINGJOBSTOUPDATE
select EJ.ID, COALESCE(SERVERMODIFIEDDATE, CLIENTPROCESSENDDATE, @NOW)
from dbo.EMAILJOB EJ
inner join dbo.EMAILSTATUSGENERALPURPOSE S on S.ID = EJ.STATUSID
where S.[STATUS] = 10 and EJ.[STATUS] <> 11
insert into @UPLOADINGJOBSTOUPDATE
select EJ.ID, COALESCE(SERVERMODIFIEDDATE, CLIENTPROCESSENDDATE, @NOW)
from dbo.EMAILJOB EJ
inner join dbo.EMAILSTATUSTRANSACTIONAL S on S.ID = EJ.STATUSID
where S.[STATUS] = 10 and EJ.[STATUS] <> 11
update dbo.EMAILJOB set [Status] = 11, [WSDateLastAccessed] = J.WSDateLastAccessed
from dbo.EMAILJOB EJ
inner join @UPLOADINGJOBSTOUPDATE J on J.ID = EJ.ID
insert into dbo.EmailJobStatus (EmailID, EmailJobID, CreateDate, UpdateDate, Status)
select EJ.EmailID, EJ.ID, J.WSDateLastAccessed, J.WSDateLastAccessed, 'Uploading'
from dbo.EMAILJOB EJ
inner join @UPLOADINGJOBSTOUPDATE J on J.ID = EJ.ID
--------------------------------------------------------------
--Accepted
declare @ACCEPTEDJOBSTOUPDATE table (ID int, WSDateLastAccessed datetime)
insert into @ACCEPTEDJOBSTOUPDATE
select EJ.ID, COALESCE(SERVERMODIFIEDDATE, CLIENTPROCESSENDDATE, @NOW)
from dbo.EMAILJOB EJ
inner join dbo.EMAILSTATUSGENERALPURPOSE S on S.ID = EJ.STATUSID
where S.[STATUS] = 1 and EJ.[STATUS] <> 4
insert into @ACCEPTEDJOBSTOUPDATE
select EJ.ID, COALESCE(SERVERMODIFIEDDATE, CLIENTPROCESSENDDATE, @NOW)
from dbo.EMAILJOB EJ
inner join dbo.EMAILSTATUSTRANSACTIONAL S on S.ID = EJ.STATUSID
where S.[STATUS] = 1 and EJ.[STATUS] <> 4
update dbo.EMAILJOB set [Status] = 4, [WSDateLastAccessed] = J.WSDateLastAccessed
from dbo.EMAILJOB EJ
inner join @ACCEPTEDJOBSTOUPDATE J on J.ID = EJ.ID
insert into dbo.EmailJobStatus (EmailID, EmailJobID, CreateDate, UpdateDate, Status)
select EJ.EmailID, EJ.ID, J.WSDateLastAccessed, J.WSDateLastAccessed, 'Accepted'
from dbo.EMAILJOB EJ
inner join @ACCEPTEDJOBSTOUPDATE J on J.ID = EJ.ID
--------------------------------------------------------------
--Completed
declare @COMPLETEDJOBSTOUPDATE table (ID int, WSDateLastAccessed datetime)
insert into @COMPLETEDJOBSTOUPDATE
select EJ.ID, COALESCE(SERVERMODIFIEDDATE, CLIENTPROCESSENDDATE, @NOW)
from dbo.EMAILJOB EJ
inner join dbo.EMAILSTATUSGENERALPURPOSE S on S.ID = EJ.STATUSID
where S.[STATUS] = 3 and EJ.[STATUS] <> 7
insert into @COMPLETEDJOBSTOUPDATE
select EJ.ID, COALESCE(SERVERMODIFIEDDATE, CLIENTPROCESSENDDATE, @NOW)
from dbo.EMAILJOB EJ
inner join dbo.EMAILSTATUSTRANSACTIONAL S on S.ID = EJ.STATUSID
where S.[STATUS] = 3 and EJ.[STATUS] <> 7
--Fix for bug#608197, update datetime should be current datetime to update correct data in NETCOMMUNITYEMAILJOBRECIPIENT table via integration service.
update dbo.EMAILJOB_RECIPIENT
set [SENT] = 1,
SENTDATE = J.WSDateLastAccessed,
UPDATEDATE = @NOW
from dbo.EMAILJOB_RECIPIENT EJR
inner join @COMPLETEDJOBSTOUPDATE J on J.ID = EJR.EmailJobID;
update dbo.EMAILJOB set [Status] = 7, [WSDateLastAccessed] = J.WSDateLastAccessed
from dbo.EMAILJOB EJ
inner join @COMPLETEDJOBSTOUPDATE J on J.ID = EJ.ID
insert into dbo.EmailJobStatus (EmailID, EmailJobID, CreateDate, UpdateDate, Status)
select EJ.EmailID, EJ.ID, J.WSDateLastAccessed, J.WSDateLastAccessed, 'Completed'
from dbo.EMAILJOB EJ
inner join @COMPLETEDJOBSTOUPDATE J on J.ID = EJ.ID
--------------------------------------------------------------
--Update conditional parent jobs
declare @PARENTJOBSTOUPDATE table (ID int);
-- First we will set it to complete if any of its jobs have just been completed
declare @PARENTJOBS table (ID int, WSDateLastAccessed datetime)
insert into @PARENTJOBS
select PARENTJOB.ID, max(CHILDJOB.WSDateLastAccessed)
from [dbo].[EMAILJOB] as PARENTJOB
inner join [dbo].[EMAILJOB] as CHILDJOB
on CHILDJOB.CONDITIONALPARENTJOBID = PARENTJOB.ID
inner join @COMPLETEDJOBSTOUPDATE C on C.ID = CHILDJOB.ID
where PARENTJOB.[STATUS] in (4, 12)
group by PARENTJOB.ID;
update [dbo].[EMAILJOB]
set [STATUS] = 7, [WSDateLastAccessed] = PJ.WSDateLastAccessed
from EMAILJOB J
inner join @PARENTJOBS PJ
on J.ID = PJ.ID;
insert into dbo.EmailJobStatus (EmailID, EmailJobID, CreateDate, UpdateDate, Status)
select EJ.EmailID, EJ.ID, PJ.WSDateLastAccessed, PJ.WSDateLastAccessed, 'Completed'
from dbo.EMAILJOB EJ
inner join @PARENTJOBS PJ on PJ.ID = EJ.ID;
insert into @PARENTJOBSTOUPDATE
select ID from @PARENTJOBS;
delete from @PARENTJOBS;
-- Now we will set it to exception if none of its jobs are completed yet but some have an exception
insert into @PARENTJOBS
select PARENTJOB.ID, max(CHILDJOB.WSDateLastAccessed)
from [dbo].[EMAILJOB] as PARENTJOB
inner join [dbo].[EMAILJOB] as CHILDJOB
on CHILDJOB.CONDITIONALPARENTJOBID = PARENTJOB.ID
inner join @ERRORJOBSTOUPDATE E on E.ID = CHILDJOB.ID
where PARENTJOB.[STATUS] in (4, 12)
group by PARENTJOB.ID;
update [dbo].[EMAILJOB]
set [STATUS] = 14, [WSDateLastAccessed] = PJ.WSDateLastAccessed
from EMAILJOB J
inner join @PARENTJOBS PJ
on J.ID = PJ.ID;
insert into dbo.EmailJobStatus (EmailID, EmailJobID, CreateDate, UpdateDate, Status)
select EJ.EmailID, EJ.ID, PJ.WSDateLastAccessed, PJ.WSDateLastAccessed, 'Exception on server'
from dbo.EMAILJOB EJ
inner join @PARENTJOBS PJ on PJ.ID = EJ.ID;
insert into @PARENTJOBSTOUPDATE
select ID from @PARENTJOBS;
delete from @PARENTJOBS;
--Now we need to move it to Partial success if it already has an exception
--and something has been completed OR if it already has a completion and something has an exception
insert into @PARENTJOBS
select PARENTJOB.ID, max(CHILDJOB.WSDateLastAccessed)
from [dbo].[EMAILJOB] as PARENTJOB
inner join [dbo].[EMAILJOB] as CHILDJOB
on CHILDJOB.CONDITIONALPARENTJOBID = PARENTJOB.ID
inner join @ERRORJOBSTOUPDATE E on E.ID = CHILDJOB.ID
where PARENTJOB.[STATUS] = 7
group by PARENTJOB.ID;
insert into @PARENTJOBS
select PARENTJOB.ID, max(CHILDJOB.WSDateLastAccessed)
from [dbo].[EMAILJOB] as PARENTJOB
inner join [dbo].[EMAILJOB] as CHILDJOB
on CHILDJOB.CONDITIONALPARENTJOBID = PARENTJOB.ID
inner join @COMPLETEDJOBSTOUPDATE C on C.ID = CHILDJOB.ID
where PARENTJOB.[STATUS] = 14
group by PARENTJOB.ID;
update [dbo].[EMAILJOB]
set [STATUS] = 18, [WSDateLastAccessed] = PJ.WSDateLastAccessed
from EMAILJOB J
inner join @PARENTJOBS PJ
on J.ID = PJ.ID
insert into dbo.EmailJobStatus (EmailID, EmailJobID, CreateDate, UpdateDate, Status)
select EJ.EmailID, EJ.ID, PJ.WSDateLastAccessed, PJ.WSDateLastAccessed, 'Partially completed'
from dbo.EMAILJOB EJ
inner join @PARENTJOBS PJ on PJ.ID = EJ.ID;
insert into @PARENTJOBSTOUPDATE
select ID from @PARENTJOBS
--------------------------------------------------------------
--return a table of EmailJob IDs to clean up their views and tables that were generated
--errored jobs
select S.ID
from @ERRORJOBSTOUPDATE S
union
--completed jobs
select S.ID
from @COMPLETEDJOBSTOUPDATE S
union
-- conditional parent jobs
select S.ID
from @PARENTJOBSTOUPDATE S
left outer join (
select P.ID from @PARENTJOBSTOUPDATE P
inner join dbo.EMAILJOB EJ on EJ.CONDITIONALPARENTJOBID = P.ID
where EJ.STATUS not in (14, 8, 7)
) OPENJOBS
on OPENJOBS.ID = S.ID
where OPENJOBS.ID is null
end