Transactions and updating two or more MS SQL servers

From: Pall Bjornsson (palli_at_oddi.is)
Date: 02/02/05

  • Next message: Craig Berntson: "Re: Transactions and updating two or more MS SQL servers" 
    Date: Wed, 2 Feb 2005 11:55:52 -0000

    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: Craig Berntson: "Re: Transactions and updating two or more MS SQL servers"

    Relevant Pages

    • Re: Connection issues between .Net framwok and SQl Express 2005
      ... You will no longer need the database MDF file name in the connection string. ... simply attach the ASPNETDB.mdf to SQL Server permenantly, ... as USER INSTANCE on existing SQL Server Express, ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: Performance of ODBC
      ... if you have a software firewall; forward 1433/1434 to your server ... I've now created my own server with SQL Server 2005 Express, ... glitch concerns the Upsizing Wizard. ... I originally set up my ODBC connection via ...
      (microsoft.public.access.adp.sqlserver)
    • SQL 2005 Express and VS .Net 2003 wizard error More options
      ... Open the server explorer panel ... Go to the Provider tab and select SQL NAtive Client (DON'T use oledb ... Go back to the Connection tab and carry on setting up the connection ... I have installed Visual Studio 2005 Professional and then Visual Studio ...
      (microsoft.public.sqlserver)
    • Re: Timeout problem with SS2K, VS03
      ... I noticed calls in sql profiler: ... This is the server side debug stored procedure. ... a timeout issue that I cannot identify the source of. ... transaction is committed and the connection is closed cleanly and all ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: Cannot connect to SQL Express on 2003 Machine from XP machine
      ... listening on UDP 1434 using netstat -abn, ... And i double checked the connection string, and i did have it the right ... But I have another 2000 server that I can connect to fine, ... I am trying to connect from an XP SP2 machine remotely to a SQL Express ...
      (microsoft.public.sqlserver.connect)