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?