Profiler shows SQL statement, but not registered

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Profiler shows SQL statement, but not registered

Author

Topic

mkenyon2
Starting Member

5 Posts

Posted - 2010-07-14 : 11:38:56

I have posted about this on Channel9, but the input has dried up.
[url]http://channel9.msdn.com/forums/TechOff/558777-SQL-transaction-batch-doesnt-register-some-actions/[/url]

Basically, we have agents who sell for us. They have a bond they draw against, and we send them the units to sell. When they sell them, a 'Cash' transaction occurs.

So, let's say our data entry person enters the transaction. It is linked to a transmittal. (Basically, a bank deposit.)

So, they pick the Agent #, Transmittal #, the number of items, and the amount of money submitted.

The VB6 app submits SQL like this:
INSERT INTO AcctRptHeader -- Summary of this transaction
INSERT INTO AcctTransaction
UPDATE TransmittalNbr SET TransTotalAmount = TransTotalAmount + CashSubmitted
WHERE TransNbr = @TransNbr
UPDATE AppAgency SET AvailBond = AvailBond + CashSubmitted
WHERE AppAgencyID = @AppAgencyID

All well and good. Now, if the data entry person then realizes they've made a mistake, they go and change that transaction. VB6 does this:
UPDATE AppAgency SET AvailBond = AvailBond + -OldCashSubmitted
WHERE AppAgencyID = @AppAgencyID -- This sometimes fails
UPDATE TransmittalNbr SET TransTotalAmount = TransTotalAmount +
-OldCashSubmitted
WHERE TransNbr = @TransNbr -- This sometimes fails
DELETE FROM AcctTransaction -- This always works
DELETE FROM AcctRptHeader -- This always works
-- ... Then it processes the updated data just as if it were a new entry.

Every time I do this, let's say I change a transaction 5 times, I can see all of this SQL hitting the server when running profiler. (Screen shots are available in the link.)

The first change goes through correctly, but the second change does not. The rollback doesn't happen, but the additional amounts do. So, the old amounts are never removed before the corrected amount is added.

Of course, this scenerio doesn't happen. If there is a correction, it may happen only once. But, for whatever reason, it has happened, throwing things into chaos.

Some specifics, the database was MS SQL 2000, but is now running on SQL 2005. I have tried, and received the same results with it running in 2000 compatibility mode or 2005 mode.

There are triggers on the tables, replicating changes to history tables. (Mentioned in the link.) I have tried to adjust the triggers so that they do not cause loops, but I don't know how to monitor what they are doing.

Let's say the user needs to enter 18. They first enter 28, save, then try to enter 18 again, but enter 218. They save, and finally enter the correct amount of 18, but now the totals are messed up. (Yes, this has happened...

)

Using the history table I can see the following:
ChangeDate AppAgency.AvailBond
7/17/2010 11:00:12 AM 0
7/17/2010 11:01:33 AM 28 -- Wrong amount
7/17/2010 11:02:44 AM 0 -- Rollback worked (Change 1)
7/17/2010 11:02:45 AM 218 -- Wrong amount again (Change 1)
7/17/2010 11:04:12 AM 236 -- Correct amount of 18 entered, but rollback didn't happen, so amount just increases (Change 2)

Like I mentioned, I've fleshed out a lot of detail and scripts in the thread after the link, but I was hoping to see if I could trouble shoot this some more. Profiler, at least as I know how to use it, is not helping me see what's happening.

[url]http://channel9.msdn.com/forums/TechOff/558777-SQL-transaction-batch-doesnt-register-some-actions/[/url]

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-07-14 : 11:45:04

it's an application error.

we'd need to see the trigger code, the actual SQL statements (not pseudo-code) and the schema.

maybe it's dying in a trigger.

are u using explicit transactions?

mkenyon2
Starting Member

5 Posts

Posted - 2010-07-14 : 13:02:09

In VB6
gdbcnDatabase.BeginTrans
....
If strSelectedLicenseYear = strAgentMaxLicenseYear Or InStr(strAgentType, "2-Year") > 0 Or InStr(strAgentType, "Regional") > 0 Then
strUpdateSQL = strUpdateSQL & CreateAppAgencySQL(strAgencyID, -curDifAvailBondAmount, -curDifOverShort, "", "", "rmv")
ElseIf strSelectedLicenseYear < strAgentMaxLicenseYear And InStr(strAgentType, "County") > 0 Then
strUpdateSQL = strUpdateSQL & CreateAppAgencySQL(strAgencyID, -curDifOverShort, -curDifOverShort, "", "", "rmv")
End If
If strUpdateSQL <> "" Then
gdbcnDatabase.Execute strUpdateSQL
End If
strUpdateSQL = ""
....
gdbcnDatabase.CommitTrans

Public Function CreateAppAgencySQL(ByVal strAgencyID As String, Optional ByVal curABAChangeAmount As Currency = 0, _
Optional ByVal curOVAChangeAmount As Currency = 0, Optional ByVal strReportDate As String = "", Optional ByVal strPaymentDate As String = "", Optional ByVal desc As String = "") As String
Dim strSQL As String

If desc = "rmv" Then
strSQL = "proc_AdjustAgencyAmountsRmv "
Else
strSQL = "proc_AdjustAgencyAmounts "
End If
strSQL = strSQL & " @AppAgencyID = '" & strAgencyID & "'"
strSQL = strSQL & " , @diffAvailBond = " & curABAChangeAmount & " "
strSQL = strSQL & " , @diffOverShort = " & curOVAChangeAmount & " "
strSQL = strSQL & " , @LastUpdatedBy = '" & gblUser & desc & "'"
If strReportDate <> "" Then
strSQL = strSQL & " , @LastReportDate = '" & strReportDate & "'"
End If
If strPaymentDate <> "" Then
strSQL = strSQL & " , @LastPaymentDate = '" & strPaymentDate & "' ;" & vbCrLf
End If
CreateAppAgencySQL = strSQL
End Function

mkenyon2
Starting Member

5 Posts

Posted - 2010-07-14 : 13:08:24

Also, if I wait for a while... say, 30 minutes, it starts working again, once.

Here's the proc (right now with debugging stuff added):
ALTER PROCEDURE [dbo].[proc_AdjustAgencyAmountsRmv]
@AppAgencyID uniqueidentifier,
@diffAvailBond money,
@diffOverShort money,
@LastUpdatedBy varchar(50),
@LastReportDate datetime = NULL,
@LastPaymentDate datetime = NULL
AS
BEGIN

BEGIN TRY
INSERT INTO testAppAgency
SELECT GETDATE(), AvailBondAmount, OverShortAmount FROM ApplicantAgency WHERE AppAgencyID = @AppAgencyID;

UPDATE ApplicantAgency
SET AvailBondAmount = AvailBondAmount + @diffAvailBond,
OverShortAmount = OverShortAmount + @diffOverShort,
LastUpdatedBy = @LastUpdatedBy,
LastPaymentDate =
CASE
WHEN @LastPaymentDate IS NULL THEN LastPaymentDate
ELSE @LastPaymentDate
END,
LastReportedDate =
CASE
WHEN @LastReportDate IS NULL THEN LastReportedDate
ELSE @LastReportDate
END
WHERE AppAgencyID = @AppAgencyID;

INSERT INTO testAppAgency
SELECT GETDATE(), AvailBondAmount, OverShortAmount FROM ApplicantAgency WHERE AppAgencyID = @AppAgencyID;

END TRY
BEGIN CATCH
EXECUTE usp_GetErrorInfo
END CATCH
WAITFOR DELAY '00:00:05';

END

Here's the update trigger:

USE [AdminAppsTest]
GO
/****** Object: Trigger [dbo].[tr_ApplicantAgency_Upd_Hist] Script Date: 07/14/2010 13:02:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[tr_ApplicantAgency_Upd_Hist] on [dbo].[ApplicantAgency]
INSTEAD OF UPDATE AS

BEGIN TRY

INSERT INTO ApplicantAgencyHistory
(CreateDateTime, AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn)
SELECT GETDATE(), AppAgencyID, AccountTypeID, ApplicationAgencyStatusID, AgentTypeID,
InactiveDate, BusinessName, AgentNumber, FirstName, MiddleName, LastName, BusinessPhone,
HomePhone, EmailAddress, SenateDistrict, HouseDistrict, County_PK, OverShortAmount,
AvailBondAmount, NumberofDelinquencies, ApplicationFeeRcvdDate, ForwardedToRegionDate,
SecondNoticeDate, FinalAppDispositionDate, GrandOpeningShipDate, LastPaymentDate,
InitialShipDate, LastReportedDate, RejectionComment, StateParkOfficeIndicator, InternetAccount,
PALSAgent, PALSTerminal, LastUpdatedBy, LastUpdatedOn
FROM Deleted d

UPDATE ApplicantAgency
SET AppAgencyID = i.AppAgencyID,
AccountTypeID = i.AccountTypeID,
ApplicationAgencyStatusID = i.ApplicationAgencyStatusID,
AgentTypeID = i.AgentTypeID,
InactiveDate = i.InactiveDate,
BusinessName = i.BusinessName,
AgentNumber = i.AgentNumber,
FirstName = i.FirstName,
MiddleName = i.MiddleName,
LastName = i.LastName,
BusinessPhone = i.BusinessPhone,
HomePhone = i.HomePhone,
EmailAddress = i.EmailAddress,
SenateDistrict = i.SenateDistrict,
HouseDistrict = i.HouseDistrict,
County_PK = i.County_PK,
OverShortAmount = i.OverShortAmount,
AvailBondAmount = i.AvailBondAmount,
NumberofDelinquencies = i.NumberofDelinquencies,
ApplicationFeeRcvdDate = i.ApplicationFeeRcvdDate,
ForwardedToRegionDate = i.ForwardedToRegionDate,
SecondNoticeDate = i.SecondNoticeDate,
FinalAppDispositionDate = i.FinalAppDispositionDate,
GrandOpeningShipDate = i.GrandOpeningShipDate,
LastPaymentDate = i.LastPaymentDate,
InitialShipDate = i.InitialShipDate,
LastReportedDate = i.LastReportedDate,
RejectionComment = i.RejectionComment,
StateParkOfficeIndicator = i.StateParkOfficeIndicator,
InternetAccount = i.InternetAccount,
PALSAgent = i.PALSAgent,
PALSTerminal = i.PALSTerminal,
LastUpdatedBy = i.LastUpdatedBy,
LastUpdatedOn = GETDATE() --i.LastUpdatedOn
FROM Inserted i, ApplicantAgency
WHERE ApplicantAgency.AppAgencyID = i.AppAgencyID

END TRY
BEGIN CATCH
EXECUTE usp_GetErrorInfo
END CATCH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources