Riddle me this, batman

[szlamany]

This SPROC runs once a month to post dues that were withheld in a pension check.

Occasionally the "final" update statement never happens.

How could MS SQL allow this - since the entire SPROC is contained in a BEGIN TRAN/COMMIT??

CREATE PROCEDURE CalcPayDues_P
as

Begin Tran

Set NoCount On

Declare @Pay_Period varchar(7)
Declare @DatePaid datetime
Declare @DuesBatch int

Set @Pay_Period=(Select ConfData From Funds_T Where ConfItem='CurPayDues')
Set @DatePaid=Cast(Convert(Char(10),GetDate(),101) as datetime)
Set @DuesBatch=0

Declare @IdList Table (MasId int, PayCheck varchar(7), Amount money
	, DuesDate datetime, DuesBatch int, DuesSeq varchar(1), DateFor datetime, OverPay money)

Insert into @IdList (MasId,PayCheck,Amount)
	Select PH.MasId,PH.RecType+Right('000000'+Cast(PH.RecNumber as varchar(6)),6),Amount From PayHistory_T PH
	Left Join PayCheck_T PC on PC.RecType=PH.RecType AND PC.RecNumber=PH.RecNumber
	Where PH.PayPeriod=@Pay_Period and Code='RDUE' and PC.Status not in ('R','S','V')

Update @IdList Set DuesDate=(Select Max(DuesDate) From Dues_T DU Where DU.MasId=IL.MasId)
	From @IdList IL

Update @IdList Set DuesBatch=(Select Max(DuesBatch) From Dues_T DU Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate)
	From @IdList IL

Update @IdList Set DuesSeq=(Select Max(DuesSeq) From Dues_T DU Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch)
	From @IdList IL

Update @IdList Set DateFor=(Select DateFor From Dues_T DU
			Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch and DU.DuesSeq=IL.DuesSeq)
	From @IdList IL

Update @IdList Set OverPay=(Select OverPay From Dues_T DU
			Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch and DU.DuesSeq=IL.DuesSeq)
	From @IdList IL

Insert into Dues_T
	Select IL.MasId,@DatePaid,@DuesBatch
	,'A','PD',IL.Amount,IL.Amount,@DatePaid
	,DateAdd(mm,1,IL.DateFor)
	,IL.OverPay
	,null,'D','N',IL.PayCheck,GetDate()
	From @IdList IL

Update Funds_T Set ConfData='' Where ConfItem='CurPayDues'

commit
Go

[TysonLPrice]

This SPROC runs once a month to post dues that were withheld in a pension check.

Occasionally the "final" update statement never happens.

How could MS SQL allow this - since the entire SPROC is contained in a BEGIN TRAN/COMMIT??

CREATE PROCEDURE CalcPayDues_P
as

Begin Tran

Set NoCount On

Declare @Pay_Period varchar(7)
Declare @DatePaid datetime
Declare @DuesBatch int

Set @Pay_Period=(Select ConfData From Funds_T Where ConfItem='CurPayDues')
Set @DatePaid=Cast(Convert(Char(10),GetDate(),101) as datetime)
Set @DuesBatch=0

Declare @IdList Table (MasId int, PayCheck varchar(7), Amount money
	, DuesDate datetime, DuesBatch int, DuesSeq varchar(1), DateFor datetime, OverPay money)

Insert into @IdList (MasId,PayCheck,Amount)
	Select PH.MasId,PH.RecType+Right('000000'+Cast(PH.RecNumber as varchar(6)),6),Amount From PayHistory_T PH
	Left Join PayCheck_T PC on PC.RecType=PH.RecType AND PC.RecNumber=PH.RecNumber
	Where PH.PayPeriod=@Pay_Period and Code='RDUE' and PC.Status not in ('R','S','V')

Update @IdList Set DuesDate=(Select Max(DuesDate) From Dues_T DU Where DU.MasId=IL.MasId)
	From @IdList IL

Update @IdList Set DuesBatch=(Select Max(DuesBatch) From Dues_T DU Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate)
	From @IdList IL

Update @IdList Set DuesSeq=(Select Max(DuesSeq) From Dues_T DU Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch)
	From @IdList IL

Update @IdList Set DateFor=(Select DateFor From Dues_T DU
			Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch and DU.DuesSeq=IL.DuesSeq)
	From @IdList IL

Update @IdList Set OverPay=(Select OverPay From Dues_T DU
			Where DU.MasId=IL.MasId and DU.DuesDate=IL.DuesDate and DU.DuesBatch=IL.DuesBatch and DU.DuesSeq=IL.DuesSeq)
	From @IdList IL

Insert into Dues_T
	Select IL.MasId,@DatePaid,@DuesBatch
	,'A','PD',IL.Amount,IL.Amount,@DatePaid
	,DateAdd(mm,1,IL.DateFor)
	,IL.OverPay
	,null,'D','N',IL.PayCheck,GetDate()
	From @IdList IL

Update Funds_T Set ConfData='' Where ConfItem='CurPayDues'

commit
Go

Error handling might help find it...

[szlamany]

I just took all that SPROC code and put it into a BEGIN TRAN / ROLLBACK.

I added an UPDATE to the top of the script that does this - filling the value for the pay period to process.

Update Funds_T Set ConfData='2018007' Where ConfItem='CurPayDues'

That was the pay period that just failed a week ago.

I added a SELECT under the UPDATE - which of course should not be reached based on an error in the INSERT into DUES_T (which I'm assuming would have been the spot of error).

Update Funds_T Set ConfData='' Where ConfItem='CurPayDues'
Select * From Funds_T

And of course it all worked fine.

I guess next step is to restore a test copy of the database "prior" to this run and see if I can show an error when running this code.

[PlausiblyDamp]

I added a SELECT under the UPDATE - which of course should not be reached based on an error in the INSERT into DUES_T (which I'm assuming would have been the spot of error).

My T-SQL is a bit rusty, I tend to be lazy and use EF these days, but I didn't think an error automatically cancelled a transaction or aborted a stored proc under T-SQL. I know SET XACT_ABORT can alter this behaviour regarding the transaction but I thought you would either need to be testing @@error or wrapping the code in a Try block to deal with this kind of thing.

Then again I could be completely wrong, as I said my T-SQL is very rusty.

[szlamany]

The whole thing that confuses me is that the INSERT into the DUES_T table does COMMIT. I see the $5000 worth of rows inserted. They all got inserted on "2018-07-09 05:30:03.033" (I've got a TDATE field that I fill with GETDATE() in that INSERT statement). That runs as a scheduled job in the SQL agent off hours - no one is in at 5:30 am.

The UPDATE to the FUNDS_T table - the one that fails - that WHERE clause appears in like 2 stored procedures that run many days apart.

I've asked the tech dept to get me a backup prior to that date/time to see if I can replicate this.

[szlamany]

btw - this SPROC was written (or at least last touched) in 2005. TRY/CATCH came along in MS SQL 2008.

We generally resist making changes to long running production code. The affect of the UPDATE failing stops the upcoming "filling" of that value with the next pay period (which happens a week or so later). Payroll clerk gets a warning message and I do 5 minutes of research and clear the value to allow her to proceed with her processing.

[szlamany]

I know SET XACT_ABORT can alter this behaviour regarding the transaction

SET XACT_ABORT also came along in MS SQL 2008. This SPROC written probably when whole transactions were aborted. I'll have to check my Inside MS SQL 2000 book later...

I'm looking to find the reason and make it NOT possible. Like find where an ISNULL or foreign key issue might have been happening. Hopefully when I get the backup and run this SPROC I'll see that error.

[FunkyDexter]

which of course should not be reached based on an error in the INSERT into DUES_T (which I'm assuming would have been the spot of error).

I'd have thought the error is more likely to be on the update statement since you're saying the Insert appears to have run. Although that doesn't really answer your question.

Since some of it has committed and a transaction should be doing all or nothing, my hunch is that this is not being cause by an error but rather a flaw in the logic. It looks like your logic depends on the existence of a Funds_T record with an ConfItem of CurPayDues. Is it possible that that record doesn't exist at the point the Sproc is run? Is it perhaps being created out of sequence, after the Sproc runs?

[szlamany]

The Funds_T row with ConfItem of CurPayDues is actually READ at the top of that SPROC - to determine what pay period is currently in the queue to be processed (they do one payroll a month - it's for retiree/pension type of payment).

When the payroll is run the Funds_T row MUST have a blank in that field - so that when they commit the payroll we can fill that field with the current pay period. This happens near the 3rd week of each month in preparation for creating checks in the last week of the month to be mailed (with a check date of the first of the next month - we calc and create checks in June for the July payroll).

Once July comes around - about 9 days into the month - this SPROC runs to "post" the deduction dues payment into the Union DUES_T table.

This SPROC reads the Pay Period value - does the post - and clears the value so that the calc for the next month can proceed.

It is the simplest forms of a queue - a one item queue - that gets filled and cleared only one time a month.

I did go through the effort of getting the database backup and unfortunately could not duplicate the problem. At this point I'm going to ignore it.

[FunkyDexter]

The Funds_T row with ConfItem of CurPayDues is actually READ at the top of that SPROC

Ah, didn't spot that. So you're definitely seeing records being created in Dues_T? ...which would mean there are records in @IDList, which would mean there's a value being found in Funds_T.ConfData - so there must be an appropriate ConfData record.

How could MS SQL allow this - since the entire SPROC is contained in a BEGIN TRAN/COMMIT??

It can't. I try never to say never but I've honestly never seen SQL Server miss-handle a transaction and the situation you're describing would mean the transactions weren't ACID, which is fundamental. If that were a bug in the DBMS reports would be all over the net so I don't believe the fault's in SQL Server. It's going to be somewhere else in the logic.

The fact that you're seeing records in Dues_T but the Funds_T record doesn't appear to be updated screams to me that there's something else is affecting either the Dues_T table or the Funds_T record. Either there's another process creating Dues_T records or editing the Funds_T record. My approach to this would be to do a search through your Sprocs (you're 100% Sproc based if I remember correctly) to find anything that references those tables. I'm pretty sure you already know how to do that but if not here's how.