>Hi, in the spanish newsgroups someone has send a questions about a weird behavior of VFP9 using SPT & SQLServer. We has discover that @@trancount is not reporting the real transactions involved, or VFP is sending the transactions statements too late.
>
>This is the code to probe it:
>
>

>?SQLDisconnect(0)
>*** Set the transactions to default, just to be sure
>?SQLSetProp(0,"Transactions",1)
>*** Connecting, prompt for any DSN, or set your if you want it.
>lnHandle = SQLConnect()
>*** Getting @@tranCount before setting manual transactions
>?SQLExec(lnHandle,"SELECT @@trancount as NumTran","ctrans")
>?"Expected value of TranCount=0, Actual TranCount=",cTrans.NumTran
>?SQLSetProp(lnHandle,"Transactions",2)
>*** Getting @@trancount after setting manual transactions
>?SQLExec(lnHandle,"SELECT @@trancount as NumTran","ctrans")
>?"Expected value of TranCount=1, Actual TranCount=",cTrans.NumTran
>*** Send an explicit transaction
>?SQLExec(lnHandle,"BEGIN TRANSACTION")
>*** Getting @@trancount after send explicit transaction
>?SQLExec(lnHandle,"SELECT @@trancount as NumTran","ctrans")
>?"Expected value of TranCount=2, Actual TranCount=",cTrans.NumTran
>

>
>In the previous code, the @@trancount value should be 1 after setting manual transactions, but is reporting 0, when you send an explicit transaction (the next line), @@trancount is reporting 2!! (where did I lost the first transaction??).
>
>This is not the behavior if you set the manual transaction before connect:
>
>

>?SQLDisconnect(0)
>*** Set the transactions to manual
>?SQLSetProp(0,"Transactions",2)
>*** Connecting, prompt for any DSN, or set your if you want it.
>lnHandle = SQLConnect()
>*** Getting @@tranCount after connecting, we already set manual transactions
>?SQLExec(lnHandle,"SELECT @@trancount as NumTran","ctrans")
>?"Expected value of TranCount=1, Actual TranCount=",cTrans.NumTran
>*** Send an explicit transaction
>?SQLExec(lnHandle,"BEGIN TRANSACTION")
>*** Getting @@trancount after send explicit transaction
>?SQLExec(lnHandle,"SELECT @@trancount as NumTran","ctrans")
>?"Expected value of TranCount=2, Actual TranCount=",cTrans.NumTran
>

>
>Is this a bug? it shouldn't do in that way!, am I wrong?

This is not a bug.
With Manual transaction the @@tranCount it doesn't change with simple operations of reading

clear
?SQLDisconnect(0)
*** Set the transactions to default, just to be sure
?SQLSetProp(0,"Transactions",1)
*** Connecting, prompt for any DSN, or set your if you want it.
lnHandle = SQLConnect()
*** Getting @@tranCount before setting manual transactions
?SQLExec(lnHandle,"SELECT @@trancount as NumTran","ctrans")
?"Expected value of TranCount=0, Actual TranCount=",cTrans.NumTran 
?SQLSetProp(lnHandle,"Transactions",2)
*** Getting @@trancount after setting manual transactions
* ADD a write operation
?SQLExec(lnHandle,"CREATE TABLE #DUMMY (A INT) DROP TABLE #DUMMY SELECT @@trancount as NumTran","ctrans")
?"Expected value of TranCount=1, Actual TranCount=",cTrans.NumTran 

*** Send an explicit transaction
?SQLExec(lnHandle,"BEGIN TRANSACTION") 
*** Getting @@trancount after send explicit transaction
?SQLExec(lnHandle,"SELECT @@trancount as NumTran","ctrans")
?"Expected value of TranCount=2, Actual TranCount=",cTrans.NumTran 

?SQLDisconnect(0)
*** Set the transactions to manual
?SQLSetProp(0,"Transactions",2)
*** Connecting, prompt for any DSN, or set your if you want it.
lnHandle = SQLConnect() && <<< this set @@trancount to 1
*** Getting @@tranCount after connecting, we already set manual transactions
*** with manual transaction
?SQLExec(lnHandle,"ROLLBACK SELECT @@trancount as NumTran","ctrans")
?"Expected value of TranCount=0, Actual TranCount=",cTrans.NumTran 
*** Send an explicit transaction
?SQLExec(lnHandle,"BEGIN TRANSACTION") 
*** Getting @@trancount after send explicit transaction
?SQLExec(lnHandle,"SELECT @@trancount as NumTran","ctrans")
?"Expected value of TranCount=2, Actual TranCount=",cTrans.NumTran 

Suggestion not to use it, even if the Mega gurus say that this is the way.
If they are then in good faith they are "young marmots",
if they are then in bad faith it is the usual planning for "young marmots".

The thing is evident from the same name:
"Manual"
Manual? But if @@trancount will have put to 1 in automatic
when it execute a command that changes the state of the connection?
Then:
- it is automatic under certain conditions in opening
- it is manual in closing
A good method to make themselves some evil.
Try to debug the session with SQL Profiler,
and found when the @@trancount change.

Fabio