Scheduled Job Failure ERROR: [SQLSTATE 42000] (Error 7344)

Question

I have encountered the following message from one of the failed job which is scheduled hourly.

Executed as user: EBGROUP\svc_denue6dscl003_sq. The OLE DB provider "MSDASQL" for linked server "DEERDS09" could not UPDATE table "[MSDASQL]" because of column "austrittsdatum". The data value violated the integrity constraints for the column. [SQLSTATE 42000] (Error 7344) OLE DB provider "MSDASQL" for linked server "DEERDS09" returned message "Non-nullable column cannot be updated to Null.". [SQLSTATE 01000] (Error 7412). The step failed.

This job has three steps and first two is successfully done but failed every time in 3rd step (stated below) while updating.

WITH DEPARTURES as (
    SELECT * FROM OPENQUERY(DEERDSXX, ''SELECT * FROM neue_ma.AustretendeMA ;'')
), PERSON as (
    SELECT PERSNR_WR, AUSTRITT_WR, weblogin_WR,KUENDDATZU_WR 
    FROM [DATEV_DBENGINE].[DENUE6ASXXX\WINDVSW1\DATEV\DATEN\PMS\DATENBANKEN\WIS32DATEVDB].dbo.PERSON 
    WHERE DBACCOUNT = 40 AND PK > 0
)
, JOINPERSNMT as (
SELECT lfdnr, ma_uid, name, vorname, login, letztertag, austrittsdatum, confirmed
, convert(date,Austritt_WR) as Austritt_wr
, convert(date,isnull(KUENDDATZU_WR, Austritt_wr)) as ITDeactivation
, CAST(RIGHT(RTRIM(LTRIM(PERSNR_WR)), 6) as INT) as PERNR
,ISNULL(DATEADD(HOUR, 36, AUSTRITT_WR), CAST(''29991231'' as DATETIME)) as flagdate
,  CASE WHEN SYSDATETIME() >= ISNULL(DATEADD(HOUR, 36, AUSTRITT_WR), CAST(''29991231'' as DATETIME)) THEN 10 else 0 end as confirmed_now
FROM DEPARTURES
LEFT OUTER JOIN PERSON
on person.weblogin_WR=DEPARTURES.Login)
update DEPARTURES set 
DEPARTURES.confirmed=case  confirmed_now when 10 then confirmed_now else DEPARTURES.confirmed end  -- only update when confirmed now
, DEPARTURES.ma_status=case  confirmed_now when 10 then 8 else DEPARTURES.ma_status end  -- only update when confirmed now
, DEPARTURES.austrittsdatum=JOINPERSNMT.Austritt_wr
, DEPARTURES.letztertag=JOINPERSNMT.ITDeactivation
from JOINPERSNMT 
where 
(JOINPERSNMT.Austritt_wr<>DEPARTURES.austrittsdatum or JOINPERSNMT.ITDeactivation<>DEPARTURES.letztertag 
or (JOINPERSNMT.confirmed_now=10 and JOINPERSNMT.confirmed_now<>JOINPERSNMT.confirmed)
) 
and DEPARTURES.ma_uid=JOINPERSNMT.ma_uid and DEPARTURES.login=JOINPERSNMT.LOGIN;'

I have checked the 'austrittsdatum' column which has no Null values also checked the Austritt_wr which has null values but this is leaving date and the employees who are still in the org they would not have a leaving date. It was running smoothly until yesterday evening. What would be the RC?

Answer 1

I have checked the update logic without updating the columns

WITH DEPARTURES as (
    SELECT * FROM OPENQUERY(DEERDS09, 'SELECT * FROM neue_ma.AustretendeMA ;')
), PERSON as (
    SELECT PERSNR_WR, AUSTRITT_WR, weblogin_WR,KUENDDATZU_WR 
    FROM [DATEV_DBENGINE].[DENUE6AS069\WINDVSW1\DATEV\DATEN\PMS\DATENBANKEN\WIS32DATEVDB].dbo.PERSON 
    WHERE DBACCOUNT = 40 AND PK > 0
)
SELECT DEPARTURES.lfdnr, DEPARTURES.ma_uid, DEPARTURES.name, DEPARTURES.vorname, DEPARTURES.login, DEPARTURES.letztertag, DEPARTURES.austrittsdatum, DEPARTURES.confirmed
, convert(date,PERSON.Austritt_WR) as Austritt_wr
, convert(date,isnull(PERSON.KUENDDATZU_WR, PERSON.Austritt_wr)) as ITDeactivation
, CAST(RIGHT(RTRIM(LTRIM(PERSON.PERSNR_WR)), 6) as INT) as PERNR
,ISNULL(DATEADD(HOUR, 36, PERSON.AUSTRITT_WR), CAST('29991231' as DATETIME)) as flagdate
,  CASE WHEN SYSDATETIME() >= ISNULL(DATEADD(HOUR, 36, PERSON.AUSTRITT_WR), CAST('29991231' as DATETIME)) THEN 10 else 0 end as confirmed_now
FROM DEPARTURES
LEFT OUTER JOIN PERSON
on person.weblogin_WR=DEPARTURES.Login
where 
(PERSON.Austritt_wr<>DEPARTURES.austrittsdatum or convert(date,isnull(PERSON.KUENDDATZU_WR, PERSON.Austritt_wr))<>DEPARTURES.letztertag 
or (CASE WHEN SYSDATETIME() >= ISNULL(DATEADD(HOUR, 36, PERSON.AUSTRITT_WR), CAST('29991231' as DATETIME)) THEN 10 else 0 end=10 and 
CASE WHEN SYSDATETIME() >= ISNULL(DATEADD(HOUR, 36, PERSON.AUSTRITT_WR), CAST('29991231' as DATETIME)) THEN 10 else 0 end<>confirmed)
) 
and DEPARTURES.ma_uid=ma_uid and DEPARTURES.login=LOGIN;

It returns one value for which Austritt_wr is NULL but KUENDDATZU_WR has a valid date. Which is not compliance according to business logic.