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

  • Re: Transactions and updating two or more MS SQL servers
    ... There is SQLSETPROPto set the transaction ... > I have a connection set up in the VFP application for each server, ... > If I successfully commit the first one, and then get an error committing ... I could dive into DTC API programming and have DTC tie the transactions ...
    (microsoft.public.fox.vfp.queries-sql)
  • Re: [PHP] Improving development process / help with developer setup
    ... SVN) and the reason to use samba is that doing this allows the developer ... stay with windows, install XAMPP. ... switch to linux with the development stack, enable a windows server to be ... where an entire publish-and-test on each commit ...
    (php.general)
  • Re: [PHP] Improving development process / help with developer setup
    ... stay with windows, install XAMPP. ... switch to linux with the development stack, enable a windows server to ... where an entire publish-and-test on each commit ...
    (php.general)
  • [GIT PULL] Please pull NFS client changes...
    ... commit 357f54d6b38252737116a6d631f6ac28ded018ed ... NFS fix the setting of exchange id flag ... returned by the server for validity. ... client ID instance when recovering a client ID. ...
    (Linux-Kernel)
  • Re: Spolskys last (it says here) opinion piece
    ... feature request - maps to exactly one diff. ... No, I commit them, but I keep a string of diff files as provenance. ... That's on a server. ... IDEs to board layout software, it's unfortunately become a de facto ...
    (comp.dsp)