Re: Transactions and updating two or more MS SQL servers
From: Craig Berntson (iamcraig_at_iamcraigberntson.com)
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" <email@example.com> 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 > >