Sporadic INSERT EXEC failed because the stored procedure altered the schema of the target table

Question

Hi everybody,

We're getting this error sporadically. The outer stored procedure has a table variable and it also has a temporary table called #Results which is used to hold results of another stored procedure, e.g.

DROP TABLE IF EXISTS #Results;
			CREATE TABLE #Results
				(
					TransactionType VARCHAR(20)
					, ProviderId VARCHAR(20)
					, NpisItemNumber VARCHAR(20)
					, NpisItemEntityTypeCode VARCHAR(20)
					, TypeIndividualFirstName VARCHAR(20)
					, TypeIndividualLastName VARCHAR(20)
					, ActiveDateString VARCHAR(8)
					, InactiveDateString VARCHAR(20)
					, XML_FileCreationDate DATETIME
					, max_XML_FileCreationDate DATETIME
				);
			INSERT INTO
				#Results
			EXECUTE dbo.usp_Generate_VAData_for_CVS_Extract
				@inp_extract_file_detail_id = @inp_extract_file_detail_id;

The inner stored procedure has 2 temporary tables with almost the same structure, but named differently. Yet we seems to get this error sporadically and now more often (we started to get this error about 4 months ago).

What can I do to fix it? Originally the outer SP used table variable and recently I changed to temp table making sure that name of that table is different than inner SP. What else can I do?

I found the following article https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/general/error-556-insert-exec-failed which has conflicting info. It says the issue is fixed in SP3 for SQL Server 2016, but at the bottom of the article it says that the problem is not going to be fixed in SQL Server 2016. What is the right info and what should I do to fix the issue?

Thanks in advance.

Answer 1

What does "SELECT @@version" report?

The best solution in the long run may be to move away from INSERT-EXEC altogether. I have an article, How to Share Data between Stored Procedures, on my web site that discusses alternatives.