Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2002
    Posts
    28

    Unanswered: Distributed transaction error

    Hi,
    I have written a procedure as below. I'm connected to a remote server server1 with valid

    login id and permissions. When I run the procedure, I get the following error.

    Server: Msg 7391, Level 16, State 1, Procedure procAddMissingAssociates, Line 09
    The operation could not be performed because the OLE DB provider 'SQLOLEDB' does not support

    distributed transactions.
    [OLE/DB provider returned message: Distributed transaction error]

    MSDTC is enabled on both the server, the local as well as the remote ones.
    In the example below ASSOCIATE_ID in table2 is a primary key.

    Please help,
    Thanks
    P.C. Vaidyanathan

    CREATE PROCEDURE procAddMissingAssociates
    AS
    DECLARE @USERID INT

    BEGIN
    SET TRANSACTION ISOLATION LEVEL repeatable read

    BEGIN DISTRIBUTED TRANSACTION
    SET XACT_ABORT ON

    DECLARE MISSING_CURSOR CURSOR FOR
    SELECT USR_ID
    FROM SERVER1.DB1.DBO.TABLE1
    WHERE USR_ID NOT IN (SELECT COLUMN2
    FROM TABLE2)

    OPEN MISSING_CURSOR
    FETCH FROM MISSING_CURSOR
    INTO @USERID

    WHILE (@@FETCH_STATUS=0)
    BEGIN
    INSERT INTO TABLE2 (ASSOCIATE_ID,
    REDEEM_POINTS,
    UPDATED_DATE)
    VALUES (@USERID,
    0,
    GETDATE())
    FETCH NEXT FROM MISSING_CURSOR
    INTO @USERID
    END
    CLOSE MISSING_CURSOR
    DEALLOCATE MISSING_CURSOR
    COMMIT TRANSACTION
    END
    GO

  2. #2
    Join Date
    Mar 2002
    Location
    Egersund, Norway
    Posts
    4

    Lightbulb

    if your servers connect to each other trough the Firewall, check that all necessary ports are open in both directions. As usually it is 1433 and 134

  3. #3
    Join Date
    May 2002
    Posts
    25
    SERVER1.DB1.DBO.TABLE1


    are both sql servers on the same network and starting as the same user? how are you authenticating with server1? linked server?

    the above post is right, if you are on seperate networks, you'll need to have access to the other machine thru the firewall.

    if you cant/dont want to setup a linked server, you can also use OPENROWSET in an encrypted view.

    if thats not the problem.. from another forum

    "To work around this problem, set XACT_ABORT to ON before the transaction. This causes SQL Server to terminate the surrounding transaction when an error occurs while processing the data modification statement. If SET XACT_ABORT is ON, SQL Server does not require nested transaction support from the OLE DB Provider. "


    Also, try the following:

    http://dbforums.com/t361216.html

Posting Permissions

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