Re: Transactions and updating two or more MS SQL servers

From: Craig Berntson (
Date: 02/02/05

Date: Wed, 2 Feb 2005 09:02:40 -0700

As you've learned, you'll need to use the DTC. Perhaps the easiest way is to
put the data update code into a COM component and host it in COM+. My
articles on the MSDN web site will get you started.

Craig Berntson
MCSD, Visual FoxPro MVP
Salt Lake City Fox User Group
"Pall Bjornsson" <> wrote in message 
> Hi !
> I have an VFP 6 application that is performing data writes to two seperate 
> MS SQL 2000 servers.
> I have a connection set up in the VFP application for each server, meaning 
> I have two SQL connection handles.
> I enclose the data updates on each server within Begin Transaction / 
> Commit Transaction pair.
> If I successfully commit the first one, and then get an error committing 
> the second one I'm in trouble as I can't rollback what's allready been 
> committed.
> How can I make absolutely sure that either both the commits succeed, or 
> they are both rolled back?
> What I have found out reading BOL and more, are the following 
> possibilities or ideas:
> 1. I could start a distributed transaction on ServerA and let DTC handle 
> it, but then I would have to perform the data updates on ServerB from a 
> linked server connection on ServerA to ServerB.
> 2. I could dive into DTC API programming and have DTC tie the transactions 
> together.
> 3. If there is some kind of "COMMIT PREPARE" SQL command I could use via 
> SQLEXEC() from VFP, I could manually do what DTC does for me i.e. issue 
> that commit prepare to all the servers and when all of those have returned 
> successfully, issue the final and actual COMMIT. Is there such a command ?
> The best way for me right now would be number 3, if there is such a 
> command. If not, then I probably go for number 1.
> Are there other options?
> Is number 1 the way to go ?
> Regards,
> Palli