egdigital
asked on
Execute stored procedure (linked server) within another stored procedure
I have 2 linked servers and need to call a stored procedure on 1 linked server within a stored procedure of another server. Everytime I attempt this i get the following error:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTran saction returned 0x8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
I know the 2 servers are linked because i can execute the remote stored procedure in a sql editor but not within another stored procedure. I also followed all steps outlined in: http://support.microsoft.com/kb/816701 but continue to get this exception. Any help would be greatly appreciated.
Thanks
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTran
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
I know the 2 servers are linked because i can execute the remote stored procedure in a sql editor but not within another stored procedure. I also followed all steps outlined in: http://support.microsoft.com/kb/816701 but continue to get this exception. Any help would be greatly appreciated.
Thanks
My guess is that both stored procedures use a transaction. If you execute one inside the other, those transactions will interfere. Try removing the use of transactions from at least one of them.
First, you should check that MSDTC service is up and running
Then, in linked server properties / server options, check the RPC and RPC out, after all you try to run a stored proc on another comp.
can you post the piece of code that troubles you ?
sorry :D
i did not read all
ok.
so you did as in the article.
but then,
you still have to make some configs on both servers.
i assume that you have win2003
so, you have to go to:
Administrative Tools / Component services / Computer / My Computer
Right click on My Computer / Properties
And there is MSDTC tab.
There you should see Transaction Configuration and a button Security Configuration.
Clik the button, and in the window that appears, there are some options:
You check all "Allow/Enable" options
And there are 3 choices for Authentication, and you choose "No Authentication required."
i did not read all
ok.
so you did as in the article.
but then,
you still have to make some configs on both servers.
i assume that you have win2003
so, you have to go to:
Administrative Tools / Component services / Computer / My Computer
Right click on My Computer / Properties
And there is MSDTC tab.
There you should see Transaction Configuration and a button Security Configuration.
Clik the button, and in the window that appears, there are some options:
You check all "Allow/Enable" options
And there are 3 choices for Authentication, and you choose "No Authentication required."
ASKER
otana -
Im not sure what u mean by removing a transaction from one of the procedures.
popa duhu -
RPC and RPC Out are both checked in the server options of the linked server. I do not see MSDTC in the list of services however it enabled under the application server (Enable network DTC access - checked)
The code that is causing a problem is this:
Procedure x (on server b2)
..... some code
Insert Into #Temp
EXEC j19.location.eg.sp_LocTagS earch @Tags
.... some more code
end
Now when on server b2 in sql editor I can execute:
EXEC j19.location.eg.sp_LocTagS earch 'X'
And this returns results as expected.
Im not sure what u mean by removing a transaction from one of the procedures.
popa duhu -
RPC and RPC Out are both checked in the server options of the linked server. I do not see MSDTC in the list of services however it enabled under the application server (Enable network DTC access - checked)
The code that is causing a problem is this:
Procedure x (on server b2)
..... some code
Insert Into #Temp
EXEC j19.location.eg.sp_LocTagS
.... some more code
end
Now when on server b2 in sql editor I can execute:
EXEC j19.location.eg.sp_LocTagS
And this returns results as expected.
ASKER
popa duhu -
I set all all/enable options as well as the no authentication required on both servers and still receive:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTran saction returned 0x8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
I set all all/enable options as well as the no authentication required on both servers and still receive:
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTran
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
sorry,
when i said MSDTC i meant Distributed Transaction Coordinator (msdtc.exe is the file name)
So, there should be a service named Distributed Transaction Coordinator
After you checked everything on both servers in Component Services, you should restart the DTC service on both servers.
when i said MSDTC i meant Distributed Transaction Coordinator (msdtc.exe is the file name)
So, there should be a service named Distributed Transaction Coordinator
After you checked everything on both servers in Component Services, you should restart the DTC service on both servers.
ASKER
That was running on both servers but I restarted anyway and still get the same error
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.