Error During Upgrade from App Portal 2015 to 2015 R2 or 2016

Yes

Summary

Error: Cannot insert the value NULL into column 'NoteDate', table 'AppPortal.dbo.WD_RequestNotes'; column does not allow nulls. INSERT fails.

Symptoms

During an upgrade from 2015 to 2015 R2 or 2016, the database upgrade fails producing the following message:

Error: Cannot insert the value NULL into column 'NoteDate', table 'AppPortal.dbo.WD_RequestNotes'; column does not allow nulls. INSERT fails.
The statement has been terminated.

Cause

This may occur when the database being upgraded has some invalid or missing data in the WD_ApprovalProcess table.

Resolution

Workaround

This issue can be worked around by running the following query prior to running the updater:

DECLARE @theDate int
SET @theDate = 60

Update WD_ApprovalProcess
set DateProcessed=DATEADD(minute,@theDate,A.DateTime)
FROM
WD_PackageRequests A
JOIN
WD_ApprovalProcess B ON A.RequestID=B.RequestId_FK
where B.Processed = 1 and [Action] = 1 AND
B.Notes IS NOT NULL AND DateProcessed IS NULL AND
not exists (select RequestID_FK from WD_RequestNotes where WD_RequestNotes.RequestID_FK = B.RequestID_FK
and WD_RequestNotes.NoteDate = B.DateProcessed AND WD_RequestNotes.ClassID = 1)

Update WD_ApprovalProcess
set DateProcessed=DATEADD(minute,@theDate,A.DateTime)
FROM
WD_PackageRequests A
JOIN
WD_ApprovalProcess B ON A.RequestID=B.RequestId_FK
where B.Processed = 1 and [Action] = 2 AND
B.Notes IS NOT NULL AND DateProcessed IS NULL AND
not exists (select RequestID_FK from WD_RequestNotes where WD_RequestNotes.RequestID_FK = B.RequestID_FK
and WD_RequestNotes.NoteDate = B.DateProcessed AND WD_RequestNotes.ClassID = 2)

Additional Information

This issue can also occur when running a service pack upgrader e.g. App Portal 2015 R2 SP4 or upgrading to App Portal 2016

Error During Upgrade from App Portal 2015 to 2015 R2 or 2016