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
