You should use VFP transaction commands in addition to SQL Server commands.

Take a look at:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnfoxtk00/html/ft00i6.asp


>Hi all,
>
>I want use SPT with Transactions= DB_TRANSMANUAL, but ...
>
>( metacode )
>
>1. h=SQLCONNECT(anyServer)
>2. SQLSETPROP(h,"Transactions",DB_TRANSMANUAL)
>3. SQLEXEC(h,"UPDATE tabName SET fName=NewValue WHERE condition")
>
>Case a)
> SQLROLLBACK(h)
> * OK undo update
>
>Case b)
> SQLDISCONNECT(h)
> * NOK update is commited !!!!!!!!! ( send IF @@TRANCOUNT > 0 COMMIT TRAN to SQL server )
>
>case c)
> QUIT()
> * NOK update is commited !!!!!!!!! ( send IF @@TRANCOUNT > 0 COMMIT TRAN to SQL server )
>
>case d)
> * Task Manager kill VFP not hang
> * NOK update is commited !!!!! ( send IF @@TRANCOUNT > 0 COMMIT TRAN to SQL server )
>
>case d)
> * Task Manager kill VFP hang
> * OK undo update
>case f)
> * hw powerdown
> * OK undo update
>
>It is true for every VFP release.
>
>Now I uses DB_TRANSAUTO and SPT "BEGIN TRAN" command.
>
>I am mistaking something? It is correct ?
>
>Fabio