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
[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.
CREATE PROCEDURE procAddMissingAssociates
DECLARE @USERID INT
SET TRANSACTION ISOLATION LEVEL repeatable read
BEGIN DISTRIBUTED TRANSACTION
SET XACT_ABORT ON
DECLARE MISSING_CURSOR CURSOR FOR
WHERE USR_ID NOT IN (SELECT COLUMN2
FETCH FROM MISSING_CURSOR
INSERT INTO TABLE2 (ASSOCIATE_ID,
FETCH NEXT FROM MISSING_CURSOR
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. "