An INSERT EXEC statement cannot be nested after upgrade and removing link server

Question

Hi ,

I am receiving "An INSERT EXEC statement cannot be nested".
I know the reason as there are 2 stored procedures that call each other and both have INSERT EXEC and it is not recommended .
However currently this code is in Production with sql server 2014 and no issue.
The set up is

• SPThirdParty : is on another server and belongs to another provider --> This is on Database A on Server A
• SPCaller which is our stored procedure calls SPThirdParty and uses Insert EXEC --> This is on Database B on Server B
• There is a link Server between these 2 Databases and a synonym is created on Server B for SPThirdParty . Surprisingly there has never been issue with Insert Exec nested error till now.
  The code is in production for years.

Currently we decide to upgrade to SQL Server 2019
Also both database A and B are now on the same server so we removed the link Server. I am currently testing the upgrade.
All of a sudden "An INSERT EXEC statement cannot be nested." error shows up.

When I researched this error I saw all over the internet that nested Insert Exec idoes not work and it seems that it should not related to upgrade unless I am missing something. So it does not make any sense why there was no error before. My questions are:
1- How is it working in production without an issue and why it is not working after the upgraded? Is it because we do not have link Server any more?
2- We can not change SPThirdParty what would be the best solution for us to have minimal changes. Is OpenQuery my only choice? If it is do I have to create link server to the same server? I

Any input is much appreciated!

Thanks,

Answer 1

With the linked server there was no problem with nested INSERT-EXEC. When you have a linked server, there are two processes and two call stacks, so there was no nesting.

One way to resolve the issue is to set up a linked server which is a loopback to the source server, but I don't recommend this.

On my web site, I have an article How to Share Data between Stored Procedures, where you can read about various alternatives to your current setup.

Answer 2

Could you please explain why you not recommend set up a linked server which is a loopback to the source server? ( Basically create a link Server to the local machine right?)

Linked servers always mean hassle. A risk when you make a loopback connection is that you may block yourself if you poke into the work you have done in the caller.

Yes, if you don't want rewrite caller, you would need OPENQUERY (which I don't recommend) or CLR (which indeed is heavy artillery). But if you can't rewrite those procedures because they are part of a third-party application, you have no choice. But then again, you seem to be considering rewriting at least one of these procedures, since you are looking replacing INSERT-EXEC with OPENQUERY.