>""IF @@TRANCOUNT>0 COMMIT TRAN" (I've seen that double-clicking the process in Enterprise Manager and that thingie shows the last command issued. Well... I don't have such command in my code. So it must be the CursorAdapter. I couldn't find any setting to make it stop that. There is no such behaviour in RV, with automatic transactions."
>

Hi Grigore,

This is because in your scenario, view didn't even have in mind to use transactions at all. But, remote view does use transactions occasionally and , in the future, we can change implementation so it'll use transactions more often.

The following code shows exactly the same problem with remote view as you are experiencing with CursorAdapter:

CLOSE DATABASES all
CLEAR 
DELETE DATABASE TestTransaction 

?VERSION()
con=SQLCONNECT("LocalServer") && point to any available SQL Server

IF con<1
	? "FAILED to connect!!!"
	RETURN
ENDIF


SQLEXEC(con,"SET ANSI_PADDING ON")
SQLEXEC(con,"create table testtransaction1 (f1 int, f2 char(10))")
SQLEXEC(con,"create table testtransaction2 (f3 int, f4 char(10))")

CREATE DATABASE TestTransaction 

CREATE CONNECTION testcon CONNSTRING (SQLGETPROP(con,"ConnectString"))

CREATE SQL VIEW testview REMOTE CONNECTION testcon AS ;
	select * from testtransaction1,testtransaction2 ;
	WHERE  testtransaction1.f1=testtransaction2.f3
	
DBSETPROP("testview","VIEW","SendUpdates",.T.)

USE testview 
CURSORSETPROP("Buffering",5)

? "Open transaction in a wrong way."
viewconn=CURSORGETPROP("ConnectHandle")
SQLEXEC(viewconn,"BEGIN TRANSACTION")

SQLEXEC(viewconn,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count,"<======= here is your transaction is active"

INSERT INTO testview values(1,"1111",1,"2222")

SELECT testview 
?[TABLEUPDATE(.T.)],TABLEUPDATE(.T.)

SQLEXEC(viewconn,"select @@TRANCOUNT as count", "trancount")
? "Current trancount:",trancount.count,"<======= now your transaction is gone!!!"


SQLEXEC(con,"select * from testtransaction1,testtransaction2 WHERE  testtransaction1.f1=testtransaction2.f3","result")
SELECT result
LIST 


CLOSE TABLES 

SQLEXEC(con,"DROP table testtransaction1")
SQLEXEC(con,"DROP table testtransaction2")

SQLDISCONNECT(0)

VFP output:

Visual FoxPro 08.00.0000.2521 for Windows
Open transaction in a wrong way.
Current trancount:           1 <======= here is your transaction is active
TABLEUPDATE(.T.) .T.
Current trancount:           0 <======= now your transaction is gone!!!
Record#           F1 F2                  F3 F4        
      1            1 1111                 1 2222

SQL Profiler output:

SQL:BatchCompleted	select * from testtransaction1,testtransaction2  WHERE  testtransaction1.f1=testtransaction2.f3
SQL:BatchCompleted	BEGIN TRANSACTION
SQL:BatchCompleted	select @@TRANCOUNT as count
SQL:BatchCompleted	set implicit_transactions on 
RPC:Completed	exec sp_executesql N'INSERT INTO testtransaction1 (f1,f2) VALUES (@P1,@P2)', N'@P1 int,@P2 char(10)', 1, '1111      '
RPC:Completed	exec sp_executesql N'INSERT INTO testtransaction2 (f3,f4) VALUES (@P1,@P2)', N'@P1 int,@P2 char(10)', 1, '2222      '
SQL:BatchCompleted	IF @@TRANCOUNT > 0 COMMIT TRAN <========= !!!
SQL:BatchCompleted	set implicit_transactions off
SQL:BatchCompleted	select @@TRANCOUNT as count
SQL:BatchCompleted	select * from testtransaction1,testtransaction2 WHERE  testtransaction1.f1=testtransaction2.f3

The transaction is closed by TABLEUPDATE because it wasn't opened properly.

Thanks,
Aleksey.