Proc changes in a trasaction rollback w/o an exception

Question

Proc changes in a trasaction rollback w/o an exception

rkmeade-508483

SSC Rookie

Points: 26
More actions
November 19, 2008 at 6:51 pm

#203107

I'm not experienced, but have time with SQL/Oracle. I have a proc that's causing a lot of pain...it is wrapped in a transaction and inserts records in 7 tables and modifies the values in 2 others. It appears to work, but between 90-180 seconds after it completes, (and the data was successfully selected and displayed in a ADODB Recordset), the records all disappear as if the Transaction was rolled back. They are actually rolled back because Sequence values are missing when I look at the table data. The more perplexing issue is that if I stop the SP Call and extract the parms then set up a debug session in VS 2005 and step thru the proc, it works. No errors and the records all remain w/o a hitch. Does anyone have an idea why this would occur? I thought latency might play a part, but creating delays between inserts/updates did not help. This behavior occurs over-the-wire to the production version of SQL Server 2005 Standard and SQLExpress 2008 on my laptop. I have tested both Try/Catch and the legacy error trapping and the code never raises an error.
Thanks,
Roger

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

HanShi SSC-Dedicated Points: 33508 More actions · Answer 1

Are you sure the changes are committed?

We had a similar problem and found the application had some issues with opening/creating different connections (SPID's). After some time the 'old' connection timed out and caused a roll back of the changes.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 2

You have posted limited information, but there are a couple of things to try.

First, when you re-displayed the data, was it using the same connection, or a different connection? Make sure you can see it from a different connection and the connection is not using NOLOCK or READUNCOMMITTED. If you can see the reocrds, they are being committed and something else is happening.

Also, you have a transaction - is it a transaction being called explicitly using T-SQL, are you calling Connection.BeginTran in ADO, or is it a DCOM distributed transaction? You can have more than ope open transaction, and you can wrap a transaction with a distributed transaction - if you roll back nested transactions, everything is rolled back, not just the "level" of the nesting. Make sure you do not have a distributed transaction that is not being committed.

rkmeade-508483 SSC Rookie Points: 26 More actions · Answer 3

It uses the same connection from ADO. The Transaction is part of the T-SQL SP, not wrapped in the ADO call. I indicated in the original post that I could run a VS 2005 debug session and the SP worked. Add a new wrinkle...I can call the SP and pass the same parms inside the SQL Server Mgmt Studio and it works as well. (I build and copy the parameters from the ADO code that is used to call the SP from within the Mgmt Studio IDE). So...it works when run in debug, works if called from within the SQL Mgmt Studio, but fails with a ADO Call.

SQL Mgmt Studio Call:

DECLARE @OLD_AREF int,@OLD_PREF int,@OLD_SREF int,@NEW_PREF int,@NEW_SREF int,@DOCNO varchar(14)

SELECT @OLD_AREF=32986,@OLD_PREF=279,@OLD_SREF=270,@NEW_PREF=1183,@NEW_SREF=557,@DOCNO='RPN-RKM0003769'

EXECUTE [BDS].dbo.ROLL_ASSET @OLD_AREF,@OLD_PREF,@OLD_SREF,@NEW_PREF,@NEW_SREF,@DOCNO

ADO Call:

Private Sub ROLL_PN()

Dim lngOldARef As Long

Dim lngOldPRef As Long

Dim lngOldSRef As Long

Dim lngNewPRef As Long

Dim lngNewSRef As Long

Dim strDocNo As String

Dim strHickey As String

On Error GoTo Err_ROLL_PN

If cmd Is Nothing Then

Set cmd = New ADODB.Command

End If

lngOldARef = CLng(Me.cboFromAsset.Value)

lngOldPRef = lngFPREF

lngOldSRef = CLng(Me.cboFromAsset.Column(5))

lngNewPRef = lngTPREF

lngNewSRef = 557

strDocNo = Me.txtDocNo.Value

With cmd

.ActiveConnection = cnx

.CommandText = "ROLL_ASSET"

.CommandType = adCmdStoredProc

.Parameters.Append .CreateParameter("@OLD_AREF", adBigInt, adParamInput, , lngOldARef)

.Parameters.Append .CreateParameter("@OLD_PREF", adBigInt, adParamInput, , lngOldPRef)

.Parameters.Append .CreateParameter("@OLD_SREF", adBigInt, adParamInput, , lngOldSRef)

.Parameters.Append .CreateParameter("@NEW_PREF", adBigInt, adParamInput, , lngNewPRef)

.Parameters.Append .CreateParameter("@NEW_SREF", adBigInt, adParamInput, , lngNewSRef)

.Parameters.Append .CreateParameter("@DOCNO", adVarWChar, adParamInput, 14, strDocNo)

.Execute

End With

Exit_ROLL_PN:

Set cmd = Nothing

On Error GoTo 0

Exit Sub

rkmeade-508483 SSC Rookie Points: 26 More actions · Answer 4

Please review my earlier reply to another trying to assist. I posted another method that works, (Debug session originally works and I just posted a call from within SQL Server Mgmt Studio that works) plus the ADO SP call that fails. (I can include the SP with transaction, but it's a lot of code).

Thanks,

Roger

rkmeade-508483 SSC Rookie Points: 26 More actions · Answer 5

HanShi,

I added another ADO Connection inside the App and tweaked the use of Command objects as well. It appears to have fixed the error! You got me to examine the Connection object, and that's what seems to cause the failure. Thanks...and to others that took the time to respond, thanks for your inputs as well.

Roger