>I am using three cursoradapters to update a sqlserver database with changes to an order:
>ca_OrderHeader
>ca_ShipmentHeader
>ca_OrderDetail
>
>How do I wrap this into a single transaction on SqlServer?
>
>I'm basically doing the following using an odbc connection to the server(oxml is the xmladapter from which the cursors were generated and nTables is the number of cursors in the adapter) the error handling code has been stripped out :
>

> SQLSetprop(nHandle, 'Transactions', 2)
>  lRet = .T.
>  For i = 1 To nTables
>        cAlias = oxml.Tables[i].Alias
>        If lRet
>            if not Tableupdate(lnRows, llForce,cAlias,'aRowResults')
>              lRet = .F.
>            Endif
>        Endif
> Endfor
>
> If lRet
>   Sqlcommit(nHandle)
> Else
>    Sqlrollback(nHandle)
> Endif
>
> SQLSetprop(nHandle,"Transactions",1)
>

>
>However, it appears as if each tableupdate is committing the transaction rather than waiting for the manual commit..
>What am I missing?

You can do this in VFP9:

CA.UseTransactions = .F. && with this the CA don't add transaction commands

on VFP9 don't forget to set:

SQLSetprop(nHandle,"DisconnectRollback",.T.)  && send a IF TRANLEVEL()>0 ROLLBACK

On VFP8 you have to use a implicit transaction and force SQL to ignore the CA commands

* WITH SQLSetprop(m.nHandle,"Transactions",1)
IF SQLEXEC(m.nHandle,"BEGIN TRAN") && this increment the transaction semaphore
.....

=SQLEXEC(m.nHandle,"IF TRANLEVEL()>0 "+IIF(m.lRet,"COMMIT","ROLLBACK")

Fabio