>Hi Aleksey
>
>
>>Logically it is correct. All data reads/writes will be included into the same transaction until SQLCOMMIT()/SQLROLLBACK(). Even if backend doesn't start transaction physically, subsequent SQLCOMMIT()/SQLROLLBACK() do not fail. It is only a plus if back end is able to avoid unnecessary physical transactions.
>
>Sorry to nit-pick, but what you say is correct only if the server is using implicit transactions.
>
>If implicit transactions are disabled (or not supported!), then you will not get a back-end transaction unless you specifically issue a 'BEGIN TRANSACTION' command, no matter how many times you issue SQLSETPROP().
>
>Whilst I understand what you mean it is, at the very least, misleading to state that setting the connection to use manual transactions is equivalent to issuing 'Begin Transaction'.


Hi Andy,

What you are missing is the fact that after you execute SQLSETPROP(con,"Transactions",2), the server will automatically switch to implicit transaction as soon as you try to execute any command that is supposed to be tracked by a transaction.
Run the following code, and see for yourself:

con=SQLCONNECT("LocalServer")
SQLSETPROP(con,"Transactions",2)
?SQLEXEC(con,"select * from master..sysprocesses")
SQLEXEC(con,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count
SQLCOMMIT(con)
SQLSETPROP(con,"Transactions",1)
SQLEXEC(con,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count

SQL Profiler log

SQL:BatchCompleted	set implicit_transactions on <========== !!!
SQL:BatchCompleted	select * from master..sysprocesses
SQL:BatchCompleted	select @@TRANCOUNT as count
SQL:BatchCompleted	IF @@TRANCOUNT > 0 COMMIT TRAN	<========== !!!
SQL:BatchCompleted	set implicit_transactions off <========== !!!
SQL:BatchCompleted	select @@TRANCOUNT as count

In your previous test, SELECT @@TRANCOUNT didn't start transaction simply because this command is never tracked by transaction (there is nothing to lock and there is nothing to rollback/commit).

From the client point of view, SQLSETPROP(con,"Transactions",2) has the same effect as BEGIN TRANSACTION.

Thanks,
Aleksey.