Re: Transactions and updating two or more MS SQL servers

From: Craig Berntson (iamcraig_at_iamcraigberntson.com)
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
www.craigberntson.com
Salt Lake City Fox User Group
www.slcfox.org
www.foxcentral.net
"Pall Bjornsson" <palli@oddi.is> wrote in message 
news:%23fM2m4RCFHA.1292@TK2MSFTNGP10.phx.gbl...
> 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
>
>

Relevant Pages