Solved

Execute stored procedure (linked server) within another stored procedure

Posted on 2007-03-30
10
638 Views
Last Modified: 2012-05-05
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::JoinTransaction 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
0
Comment
Question by:egdigital
  • 6
  • 3
10 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 18822783
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.
0
 
LVL 3

Expert Comment

by:popa_duhu
ID: 18822789
First, you should check that MSDTC service is up and running
0
 
LVL 3

Expert Comment

by:popa_duhu
ID: 18822806
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.
0
 
LVL 3

Expert Comment

by:popa_duhu
ID: 18822811
can you post the piece of code that troubles you ?
0
 
LVL 3

Expert Comment

by:popa_duhu
ID: 18822883
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."
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:egdigital
ID: 18822947
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_LocTagSearch @Tags
  .... some more code
end

Now when on server b2 in sql editor I can execute:
EXEC j19.location.eg.sp_LocTagSearch 'X'
And this returns results as expected.

0
 

Author Comment

by:egdigital
ID: 18823085
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::JoinTransaction returned 0x8004d00a].
The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
0
 
LVL 3

Expert Comment

by:popa_duhu
ID: 18823505
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.
0
 

Author Comment

by:egdigital
ID: 18823685
That was running on both servers but I restarted anyway and still get the same error
0
 
LVL 3

Accepted Solution

by:
popa_duhu earned 500 total points
ID: 18828249
well...
maybe i miss something
but i know that i've solved once this problem.

check this link: http://support.microsoft.com/kb/873160
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL for Frequently Bought With 11 45
SQL Query stumper 3 34
SQL server is using more virtual memory. 5 63
SQL Server stored proc 2 10
Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now