Hi, Ken.

>We're working on our first production web service, to be called from our proprietary app (VFP7/SQL2000).
>
>Question - Is there any way to maintain a SQL server transaction across a web service? That is, I would like my front end application to be able to say something like WebService.BeginTransaction() then make a variety of separate calls to the web service for various updates, inserts, etc. into the database, and then be able to close it out with some sort of WebService.Commit() call. Can the connection/state/transaction be maintained across a web service in this way? If so, is it advisable, practical, etc?
>
>Otherwise, I 'm assuming I have to send all the transactions up at once to the web service and let it (associated dll) deal with transactions itself.

Fortunately, there is no way to keep a transaction open during calls in a Web Service. Indeed, there is no an implicit mechanism to keep the connection identified.

Web Services are disconnected by nature, and you should work with them that way. Every single call you do connects and disconnects from the web server the same as a web page.

Keep in mind that database transactions should always be kep to a mimimum. Now, just figure how can you ensure that things are still there between calls in a Web Service (where there is the whole Internet in the middle!). Connection can drop, the server can become unavailable, your client app can hang, etc.

If you put in place a Web Service to implement a bare-bones remote access to a database, you should do as you sugested at the end of your question: accumulate the statements, and then send them all together.

An alternative aproach could be implement a "fake" transaction following your pattern:

* This should return a handle and, server-side, add a record to 
* a table with at least a field for the handle and a memo for the statements
lnHandle = WebService.BeginTransaction()

* In each call, the WS should add the statement to the memo, based on the handle
WebService.Send( lnHandle, lcStatement1 )
WebService.Send( lnHandle, lcStatement2 )
WebService.Send( lnHandle, lcStatement3 )

* Now the WS executes all the statements within a transaction, 
* and returns whether it was Ok or not
if WebService.CommitTransaction( lnHandle )
   * Everything Ok
endif

Notice that this means that you can't pass values back until the transaction has actually being commited.

In most situations, I should avoid such approach and reply on Stored Procedures if I can.

Hope this helps,