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

    Unanswered: Inser a table from a view in a transcation

    Hello, everyone:

    I have to post this problem again because I didn't get the correct way.

    I have SQL Server 2000 on client (Windows XP) and server (Windows 2003). DTC is running on both side. There is a transcation like,

    BEGIN TRAN
    INSERT MyTable SELECT * FROM MyView
    COMMIT

    MyView get data by:

    SELECT * FROM OPENDATASOURCE ('SQLOLEDB', 'Data Source=PROD;UserID=DBDev;Password=WORLDWID').PRODD ata.dbo.CService

    PROD is the production server.

    When I run this query, there is the error message,

    Server: Msg 8501, Level 16, State 1, Line 2
    MSDTC on server 'PROD' is unavailable.
    Server: Msg 7391, Level 16, State 1, Line 2
    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].

    Any idea is appreciated.

    Thanks

    ZYT

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What is the source of PROD?

    SQL Server or some other platform

    And just to cross you i's and dot you t's

    This

    BEGIN TRAN
    INSERT MyTable SELECT * FROM MyView
    COMMIT

    Should look like this

    BEGIN TRAN
    INSERT INTO MyTable (Col list) SELECT Col list FROM MyView
    COMMIT


    No Predicate? Do you TRUNCATE myTable before this operation?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2003
    Posts
    223
    Hi, Brett:

    Thanks for replay. The data source, PROD, is SQL Server 2000. I don't truncate MyTable as run the query.

    ZYT

Posting Permissions

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