Hi all

I wonder how to treat SQL Server transactions with VFP9SP2 as front end and MS SQLServer as back end.

Specifically in a father /child scenario (invoices)
.
I have tried two approaches: cursor adapters and spt, but have many doubts.
1) in the case of CAs, I do the following

sqlexec(nHandle,'begin transaction')
SQLSetProp(nHandle,'Transactions',2) && set transactions to manual mode

then insert into the cursor adapter (father table ) and tableupdate the CA
obtain primary key from father table
then insert all records into the cursor adapter for the child table and tableupdate the CA
If OK
SQLCommit(nHandle)
Else
SQLRollback(nHandle)
endif

SQLSetProp(nHandle,'Transactions',1) && set transactions back to automatic mode

2) in the case of spt

begin transaction and set them to manual
insert records directly into father table
obtain pk
then insert records into child table
and commit or rollback the transactions, setting them back to automatic

In case there is an error at any point during the transaction, my doubtz are:

Does vfp really pick up the error as returned by the sql server and therefore sends the command to rollback the transaction?

And foremost, is it necessary to set the transactions to manual to begin with or just leaving them in automatic mode is enough?

IOW, if I leave the transactions in automatic mode, does the sql server automatically roll them back in case of an error?

I am very confused with this issue and will greatly appreciate your help

Rafael Copquin
rcopquin@fibertel.com.ar