May 23, 2008 at 3:03 am
Hi all,
I have a strange problem and hope someone can help we with this.
This is our situation (a bit simplified): We use a batch-process that executes several SP's that, among other things, inserts records in a table. Sometimes (not always reproducable!!) we notice that after the batch-process is finished, a row that has to be inserted during the batch-process is not there (anymore?).
To analyse the problem I have set-up a trace during the batch-process. I also put three triggers (INSERT, UPDATE and DELETE) on the table and each trigger saves data from the affected record to an audit table. After the batch-process is finished I can see in the trace that the Stored Procedure in question is getting started. Every statement within this Stored Procedure is also started and completed and finally the Stored Procedure itself is completed without error. During the execution of this SP I also see (in the trace) the INSERT-trigger is fired and executed without errors. Finally I find in the trace the value of the ID column for the created record. But both in the table and the audit table there is not a record created with this ID!!
During the batch-process there is not a single COMMIT TRAN or ROLLBACK TRAN logged in the trace. Also the record is not deleted. But even when the record is created and deleted afterwards I it should have been recorded in the audit table.
Can anyone shed a light?
Kind regards,
Hans
May 23, 2008 at 6:44 am
It's just guesses without some code and structure.
If you step through the process, running the steps manually, can you see the data move appropriately? That's where I'd start.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 23, 2008 at 7:03 am
Is the insert actually inserting any data? Triggers, etc. still trigger if no data qualifies.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 28, 2008 at 2:50 am
Found the problem:
The statements in the batch-process are executed under different TransactionID's while everyone thought it was under the same one. Sometimes the application ends a transaction without issuing a COMMIT. Therefor only the record created by this transaction is rolled back instead of the complete batch-process.
The development-team is going to change the code to prevent this behaviour.
May 30, 2008 at 6:09 am
At the end we found the problem is due to a bug in the connection.
See http://forums.microsoft.com/MSDN/ShowPost.aspx?PageIndex=1&SiteID=1&PageID=1&PostID=3055387
One of the transactionID's is timed-out and rolled back, but this is not reported back to the main thread.
Unfortunate sofar we only found a solution for vb.Net and our application is in VB6 :angry:. We are now looking for a work around.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply