Excepted Batch do not contains any data

Blackbaud CRM 

I made a custom batch and the good thing was that it was running fine without any errors, At one day, the client complained that when he got some exceptions in the batch at the time of commit then he was not getting any data in the excepted batch. We all know that whenever any batch committed then if that batch contains exception in any row then that row would not commit and only those rows committed which are successfully pass the validation test. Now, if you would get exception then another batch created and all those rows which contains any error then that row would jump into the excepted batch. 

So my client told me that he was not getting any data in the excepted batch, this was serious concerned for me. So tried to made my research on this issue and somehow with few tricks, i achieved this. 

First you need to create additional parameter in all the Add,Edit and Commit spec which is

"@ORIGINATINGBATCHROWGUID uniqueidentifier = null" . You would also create a formfield in all the three spec

<common:FormField FieldID="ORIGINATINGBATCHROWGUID" DataType="Guid" Hidden="true" />

Then load all the spec. You don’t need to add any business logic based on this ORIGINATINGBATCHROWGUID. Also you need to create this variable in staging table as well, please see the below mentioned line

<ComputedColumnField Description="Originating Batch Row GUID" Name="ORIGINATINGBATCHROWGUID" Expression="ID"/>

Now load all the spec, this ORIGINATINGBATCHID basically contains in OOB BATCH table, please see the below screen shot taken from SQL SERVER .

Picture (A)

 

If you see after adding “@ORIGINATINGBATCHROWGUID” parameter, you would see the above column ORIGINATINGBATCHID gets populated in OOB BATCH table. See the above highlighted rows . First highlighted row identified that this batch row contains exception and this ORIGINATINGBATCHID contains the below highlighted row BATCH ID. This is fine up till now.

We have staging table shown below

Picture (B)

So we need to pick that BATCH ID from PICTURE(A) which contains ORIGINATINGBATCHID not null, because this row contains the exception. So pick that BATCH ID and replace this in staging table so that whenever user will opened the excepted batch then it will open that batch which contains exception. Please see the below trigger which is the deciding point of this complex issue, with the help of this batch, I replaced the BATCH ID in staging table.

create trigger [dbo].[TR_BATCH_COMMITMENT_EXCEPTION_UPDATE] on [dbo].[BATCH] after insert not for replication    as     begin                         if exists (Select 1 from INSERTED where  ORIGINATINGBATCHID is not null)             begin                         declare @NEWBATCHID uniqueidentifier = (select ID from INSERTED);                         declare @ORIGINATINGBATCHID uniqueidentifier = (select ORIGINATINGBATCHID from INSERTED);                                         if exists(select 1 from dbo.USR_BATCHCOMMITMENT where BATCHID = @ORIGINATINGBATCHID)                 begin                                     declare @TempTbl table (                                         [ID] uniqueidentifier)                                                         insert into @TempTbl                                     select BC.ID from dbo.USR_BATCHCOMMITMENT  BC                                     inner join dbo.USR_BATCHCOMMITMENTBATCHSYSTEMMESSAGES BCM on BC.ID = BCM.USR_BATCHCOMMITMENTID                                     where BATCHID = @ORIGINATINGBATCHID                                                                         update dbo.USR_BATCHCOMMITMENT                                     set BATCHID = (select ID from INSERTED)                                     where ID = (select ID from @TempTbl)                         end                            else if  exists(select 1 from dbo.USR_BATCHCOMMITMENTPROGRESSREPORT  where BATCHID = @ORIGINATINGBATCHID)                         begin                                     declare @TempTbl1 table (                             [ID] uniqueidentifier)                                 insert into @TempTbl1                         select BC.ID from dbo.USR_BATCHCOMMITMENTPROGRESSREPORT BC                         inner join dbo.USR_BATCHCOMMITMENTPROGRESSREPORTBATCHSYSTEMMESSAGES BCM on BC.ID = BCM.USR_BATCHCOMMITMENTPROGRESSREPORTID                         where BATCHID = @ORIGINATINGBATCHID                                                 update dbo.USR_BATCHCOMMITMENTPROGRESSREPORT                         set BATCHID = (select ID from INSERTED)                         where ID = (select ID from @TempTbl1)                                                 end             end               end

In above trigger, you would see 2 staging table highlighted in yellow color because I had this issue in 2 custom batches. So I used the checks on this trigger.