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
GOif 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|||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