Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2003
    Posts
    6

    Unanswered: Distributed Transaction Help

    I try to use disrtibuted transaction. First,I use MSDASQL successfull:

    set XACT_ABORT on
    set ANSI_NULL_DFLT_ON on
    set ANSI_WARNINGS on
    BEGIN DISTRIBUTED TRANSACTION
    select * from OPENDATASOURCE('MSDASQL','DRIVER={SQL Server};SERVER=192.168.2.1;UID=sa;PWD=PASSWORD;'). pubs.dbo.jobs
    commit tran

    Then I try to use SQLOLEDB, But failure:

    set XACT_ABORT on
    set ANSI_NULL_DFLT_ON on
    set ANSI_WARNINGS on
    BEGIN DISTRIBUTED TRANSACTION
    select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.2.1;User ID=sa;Password=PASSWORD').pubs.dbo.jobs
    commit tran

    I get the following error message:
    The operation could not be performed because the OLE DB
    provider 'SQLOLEDB' was unable to begin a distributed
    transaction.
    [OLE/DB provider returned message: New transaction cannot
    enlist in the specified transaction coordinator. ]
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
    ITransactionJoin::JoinTransaction returned 0x8004d00a].



    but I run the code use SQLOLEDB without DISTRIBUTED TRANSACTION success:

    select * from OPENDATASOURCE('SQLOLEDB','Data Source=192.168.2.1;User ID=sa;Password=PASSWORD').pubs.dbo.jobs


    WHY? Please help me!

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    is 192.168.2.1 the server you are executing from? You can't do this to the same instance of sql server.

  3. #3
    Join Date
    Nov 2003
    Posts
    6
    Originally posted by nigelrivett
    is 192.168.2.1 the server you are executing from? You can't do this to the same instance of sql server.
    Thanks nigelrivett! I executing the code each server,but get th same result!

  4. #4
    Join Date
    Nov 2003
    Posts
    6
    WHO KNOW WHY? WHO CAN HELP ME?
    Last edited by tomcom; 11-05-03 at 00:38.

  5. #5
    Join Date
    Nov 2003
    Posts
    6
    UP

  6. #6
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    I assume dtc is running and rpc allowed.
    Is there a firewall between the servers.
    Are they on the same domain.

    I would suggest you post the message again.

    If you do a search you will find a number of possibilities but there are a lot of things that can affect this.

  7. #7
    Join Date
    Nov 2003
    Posts
    31
    Maybe we can try downloading the DTCPing.exe file from the website below and executing DTCPing on both servers. The log file which will then be displayed will tell u what is wrong with the connection.

    That's what i did and i found out my RPC was something wrong.
    My error was "There are no more end points available from the end point mapper." Just that i don't understand what this error means.

    http://support.microsoft.com/default...b;en-us;306843

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •