>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

In fact here VFPT has not done a good job.
In automatic mode VFP does not do anything,
in a manual way VFP force the ODBC to open a transaction that remains open until it receive a commit or a rollback.

For me the only way of working is in automatic mode, declaring in code all begin trans and commit and rollback all necessary.

Pay attention to the parameter "DisconnectRollback" of SQLSETPROP(),
to me, must be true forever!

If you declare the SQLSETP(0,) setting, VFP uses this as default setting for every new session