Re: Transactions and updating two or more MS SQL servers

From: Anders Altberg (x_pragma_at_telia.com)
Date: 02/02/05

  • Next message: Kam: "SQL Passthru- Insert and Update" 
    Date: Wed, 2 Feb 2005 21:00:54 +0100

    There is SQLSETPROP(handle,'Tranactions', 1 | 2) to set the transaction
    handling to Auto or Manual. If Manual you can use SQLCOMMIT(handle) or
    SQLROLLBACK(handle) to finish the tranaction.
    -Anders

    "Pall Bjornsson" <palli@oddi.is> wrote in message
    news:#fM2m4RCFHA.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
    >
    >

  • Next message: Kam: "SQL Passthru- Insert and Update"

    Relevant Pages

    • Re: Linked Server on WinXP SP2
      ... Between Web Servers and SQL Servers Running Windows Server 2003" at ... discusses ICF and DTC. ... to a distributed transaction in the case of the insert/exec statement. ... XPSP2 --> XPSP2 WORKS ...
      (microsoft.public.sqlserver.programming)
    • Re: Transactions and updating two or more MS SQL servers
      ... you'll need to use the DTC. ... > I have a connection set up in the VFP application for each server, ... > I enclose the data updates on each server within Begin Transaction / ... > Commit Transaction pair. ...
      (microsoft.public.fox.vfp.queries-sql)
    • Re: Looking for suggestions on specs
      ... heavy use of DTC without using linked server queries or two-phase commits. ... I refer to ODBC transactions. ... transaction support it ...
      (microsoft.public.sqlserver.clustering)
    • Re: MSDTC issue
      ... make sure that in the Application Server in the windows components in the ... Also that both have DTC ... I hope someone can help me with getting MSDTC to work. ... send out the error-message "New transaction cannot enlist in the ...
      (microsoft.public.biztalk.general)
    • MSDTC issue
      ... I hope someone can help me with getting MSDTC to work. ... -windows 2003 SP1 server ... send out the error-message "New transaction cannot enlist in the ... Beginning DTC Transaction ...
      (microsoft.public.biztalk.general)
    Loading