@@trancount not decrementing

[lineman]

I am using VFp to access SQL server and program is on endless loop ! - exit point is @@trancount = 0
code abstract ( with comments on result)
tx = 'SELECT @@TRANCOUNT nTrans'
SQLEXEC(nhandle,tx)
? ntrans **** result is 1
SQLEXEC(1,'commit transaction')
* also tried SQLcommit(nhandle)
SQLEXEC(nhandle,tx)
? ntrans **** result still 1

back end updates fully committed OK
any ideas ?
thanks

Richard

 

[bborissov]

SQLEXEC(1,'commit transaction')?

Where is your connection handler here?

ALWAYS check what these functions (SQL*) returns.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[lineman]

Sorry - code a bit misleading ! - I copied this from the command window and nhandle was 1 ! Checked all return values from sqlexec and these were 1 for OK

 

[bborissov]

I have no such problem:

sqhlH = SQLSTRINGCONNECT([Driver=SQL Server;Server=Server2003;database=aladinfirm0001;Trusted_Connection=yes])

SQLEXEC(sqhlH,[SELECT @@TRANCOUNT AS Test],[aaa])
BROWSE
? SQLEXEC(sqhlH,[BEGIN TRANSACTION])
SQLEXEC(sqhlH,[SELECT @@TRANCOUNT AS Test],[aaa])
BROWSE
? SQLEXEC(sqhlH,[COMMIT TRANSACTION])
SQLEXEC(sqhlH,[SELECT @@TRANCOUNT AS Test],[aaa])
BROWSE

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[lineman]

Yeah thanks - I tried your code and it works fine in command window - as expected. However when I run the programme and get to the commit procedure, it falls over again. I am checking someone elses code so I will keep looking for an explanation ! - any other hints gratefully received

Richard

 

[ESquared]

transactions can be weird because sometimes an implicit transaction is created at the same time as an explicit transaction, so that you seem to skip straight from 0 to 2.

Are you sure that you have to track transaction count in this way?