SQL Server Performance Forum – Threads Archive
Nested Stored Procedure Throws Error 7391
Hi, I have several Stored Procedures, which effectively do the same process, but on different linked servers. The linked servers are at remote locations which I access across the WAN. Each Stored Procedure deletes rows from a local table, and then inserts the results from a select statement. I have nested each of these Stored Procedures within another (Parent) Stored Procedure so that I have the choice of executing all of the (Child) Stored Procedures from the one parent Stored Procedure, or I could execute child Stored Procedures individually. I can successfully execute either the Parent, or any of the Child Stored Procedures. However, if attempt to execute the Parent Stored Procedure from within yet another Stored Procedure, I receive 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].
Msg 7391, Level 16, State 1, Procedure UserStoredProcedureName, Line 20
The operation could not be performed because the OLE DB provider ‘SQLOLEDB’ was unable to begin a distributed transaction.
I am not concerned whether the routine is executed from within a Transaction, so I have attempt to disable this, but still to no avail. The linked SQL Servers are only currently updated to Service Pack 3, and am wondering that if there is nothing wrong with what I am attempting to do, whether Service Pack 4 might solve the problem. Can anyone kindly offer any assistance?
Check the KBAs and see whether you can resolve
http://support.microsoft.com/kb/306212
http://support.microsoft.com/kb/839279 and alsohttp://www.dotnetjunkies.com/WebLog/daveadamson/archive/2005/11/22/133928.aspx blog entry. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
I have spent considerable time searching the web, and believe I have exhausted all of the links relating to the error, especially those suggested by you satya. I am now reasonably convinced that Service Pack 4 does not resolve the problem because I found managed to find one of the servers which has it installed, and the problem still exists with that server. Whilst writing this post, I realised that the first Stored Procedure uses a Transaction! And I had actually placed the execute SP statement within it. So, by moving it outside of the Transaction, it worked perfectly. Thanks for your assistance.
Appreciate your feedback and in general the error 7391 root causes are defined in those articles, hence i have suggested to check back. SP4 has other fixes too and I suggest to review README.HTML in this case. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
]]>