*** N.B. ONLY APPLICABLE WHERE STEP 3 FAILURE IS DUE TO "Operation must use an updateable query." ERROR ***
Pass to the DBA's to run the below
3 actions to take on the same day as the failure:
1)
Run the following SQL on the CPF_BACS server. This a) shows the failure that was sent to CAM and b) prepares for the job step to be re-run (i.e. deletes the record for the failed worksheet and disables the 'create worksheet' component).
USE CPF_BACS
GO
DECLARE @Typ VARCHAR(100) = 'Daily Held Debts';
DECLARE @Sdt DATETIME = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112));
DECLARE @Edt DATETIME = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112));
EXEC dbo.GetDailyDisbursementCreditorFailureTextByOutputFileType @Typ, @Sdt, @Edt;
BEGIN TRY
BEGIN TRANSACTION
DELETE rf
FROM Reports.Files rf
INNER JOIN (
SELECT ofhl.CreditorID, ofhl.DisbursementID
FROM (SELECT MAX(ID)
FROM dbo.DailyDisbursementOutputFileHistoryLogs
WHERE LOWER(OutputFileType) = LOWER(@Typ)
AND CONVERT(VARCHAR, StartTime, 112) BETWEEN CONVERT(VARCHAR, @Sdt, 112) AND CONVERT(VARCHAR, @Edt, 112)
GROUP BY CreditorID) Latest(MaxID)
INNER JOIN dbo.DailyDisbursementOutputFileHistoryLogs ofhl
ON ofhl.ID = Latest.MaxID
WHERE CONVERT(VARCHAR, ofhl.ErrorDateTime, 112) BETWEEN CONVERT(VARCHAR, @Sdt, 112) AND CONVERT(VARCHAR, @Edt, 112)
) failures
ON rf.CreditorID = failures.CreditorID
AND rf.DisbursementID = failures.DisbursementID
WHERE rf.ReportType = 4;
IF ROWCOUNT_BIG() <> 1 RAISERROR('Incorrect number of records updated', 11, 1);
INSERT SSIS_Config.dbo.SSISConfigurations
(ConfigurationFilter, ConfiguredValue, PackagePath, ConfiguredValueType)
VALUES (
N'DailyHeldDebtFileProduction',
N'True',
N'\Package\SEQ Daily Held Debt File Creation Package Logic\SEQ Create Electronic Held Debt Files\FEL For Each Held Debt Creditor\SQL Create Held Debt Worksheet.Properties[Disable]',
N'Boolean'
);
IF ROWCOUNT_BIG() <> 1 RAISERROR('Incorrect number of records updated', 11, 1);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH
2)
Re-run the APP CPF_BACS Create Daily Disbursement job from step 3
3)
Run the following SQL on the CPF_BACS server. This should return an empty result set (meaning no failures), and it also re-enables the 'create worksheet' component ready for the following day's run.
USE CPF_BACS
GO
DECLARE @Typ VARCHAR(100) = 'Daily Held Debts';
DECLARE @Sdt DATETIME = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112));
DECLARE @Edt DATETIME = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112));
EXEC dbo.GetDailyDisbursementCreditorFailureTextByOutputFileType @Typ, @Sdt, @Edt;
BEGIN TRY
BEGIN TRANSACTION
DELETE SSIS_Config.dbo.SSISConfigurations
WHERE ConfigurationFilter = N'DailyHeldDebtFileProduction'
AND PackagePath = N'\Package\SEQ Daily Held Debt File Creation Package Logic\SEQ Create Electronic Held Debt Files\FEL For Each Held Debt Creditor\SQL Create Held Debt Worksheet.Properties[Disable]';
IF ROWCOUNT_BIG() <> 1 RAISERROR('Incorrect number of records updated', 11, 1);
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
PRINT ERROR_MESSAGE();
END CATCH