Distributed transaction error

[vaidyanathanpc]

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

 

[essa2000]

Dear ;
I got these from Book On line , please see into it.

Explanation

This error can occur while processing an INSERT, UPDATE, or DELETE statement inside an explicit or implicit transaction. This indicates that the OLE DB provider does not support distributed transactions, which is needed for data modification statements inside an explicit or implicit transaction. A data modification statement can be executed against such a provider only in the case where the statement is a transaction by itself.

Action
Verify that the OLE DB provider specified supports distributed transactions. If the provider does not support distributed transactions, rewrite the data modification statement not to use distributed transactions.


Regards,
Muhammad Essa Mughal