Some others here have more experience with SQL Server and can answer better than I, but I think your approach is correct, except that I don't think you need to explicitly send 'Begin Transaction'. Setting manual transaction mode will begin the transaction for you.

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

Yes, TableUpdate() will fail if SQL Server rejects the update, then you can SQLRollback().

>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?

You need to use manual mode. Otherwise, a transaction is wrapped around every statement VFP sends to SQL Server, meaning every table (maybe every record) is committed separately. You want to commit all tables/changes as a single unit, so you need to use manual mode.

You can confirm all of this using SQL Server Profiler. You may need to turn on tracking for transactions.

>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