Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: How to read data from remote server inside a transaction?

    Hello, everyone:

    I have a local transaction,

    BEGIN TRAN
    INSERT Z_Test SELECT STATE_CODE FROM View_STATE_CODE
    COMMIT


    View_STATE_CODE points to remote SQL server named PROD. There is error when I run this query:

    Server: Msg 8501, Level 16, State 1, Line 12
    MSDTC on server 'PROD' is unavailable.
    Server: Msg 7391, Level 16, State 1, Line 12
    The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
    OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d01c].

    It looks like remote server is not available inside the local transaction. How to handle that?

    Thanks

    ZYT

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    1) make sure MSDTC is running, u can see that from SQl Service Manager.
    2) add this code before 'begin tran' , SET XACT_ABORT ON
    3) instead of 'begin tran' begin distributed tran'
    4) And SET XACT_ABORT OFF, AFTER COMMIT TRAN
    Eg:
    Code:
    SET XACT_ABORT ON
    begin distributed tran
     
    insert into sometable select * from remoteserver.database.dbo..foo
     
    commit tran
    SET XACT_ABORT OFF
    or check this site http://support.microsoft.com/default...b;en-us;839279 if ur SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service Pack 2
    Last edited by mallier; 11-23-05 at 10:02.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Mar 2003
    Posts
    223
    Hello, Mailler:

    Thanks. MSDTC is running. The query you posted doesn't work.

    ZYT

  4. #4
    Join Date
    Jun 2003
    Posts
    269

    Question re

    what error u getting now.Is SQL Server 2000 server installed on Windows Server 2003 or Windows XP Service ?
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  5. #5
    Join Date
    Mar 2003
    Posts
    223
    Hi, Mailler:

    Thanks. The error is still "MSDTC on server 'PROD' is unavailable.
    ". The SQL Server 2000 is running on Windows XP SP1. Is it the reason?

    ZYT

  6. #6
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    check the link i given in first reply
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

Posting Permissions

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