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

Distributed transaction

Hi all,
I am trying to merge data of 2 tables on different servers with an insert statement.

INSERT INTO SERVER1.db.owner.table
select s2.* from SERVER2.db.owner.table as s2
LEFT JOIN SERVER1.db.owner.table as s1
ON s1.key=s2.key
where s1.key is null

I got this error.
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

while the select query is giving the result.
I have done simmilar inserts on some other tables which worked fine

I have created sp_addlinkedserver.
and DTC set on both servers.

any help will be greatly appreciatedDid you execute this on Server1 or Server2? What OS and sp are on the respective servers?

-PatP|||Are you doing it within a transaction? You can successfully bypass engagement of MSDTC by preceeding your INSERT with SET IMPLICIT_TRANSACTIONS OFF.|||Thanks for the reply.
I have tried it on both servers .
os:w2k and sp3.
It is not with in transaction.
error still occurs.