This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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.
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.
Select @@VERSION returns Microsoft SQL Server 2016 (SP3-CU1-GDR) (KB5029187) - 13.0.7029.3 (X64)
Aug 16 2023 19:44:44
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: )
```For now I implemented the following solution, do you think it should work? I think from your article the method 4.1 should work, but it would require re-writing both procedures :(
```sql
BEGIN TRY
INSERT INTO
#Results
EXECUTE dbo.usp_Generate_VAData_for_CVS_Extract
@inp_extract_file_detail_id = @inp_extract_file_detail_id;
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 556 -- Re-run failed statement
INSERT INTO
#Results
EXECUTE dbo.usp_Generate_VAData_for_CVS_Extract
@inp_extract_file_detail_id = @inp_extract_file_detail_id;
ELSE
THROW;
END CATCH;
Yeah, a retry. I was thinking of suggesting that, but it sounded too cheesy.
A small improvement:
Again:
BEGIN TRY
INSERT INTO #Results EXEC...
END TRY
BEGIN CATCH
IF error_number() = 556 AND @done_retry_already = 0
BEGIN
SELECT @done_retry_already = 1
GOTO Again
END
END CATCH
This saves you from duplicating the INSERT EXEC statements.
Then again, in the long run, INSERT-EXEC is an accident waiting to happen for other reasons, so my recommendation to replace it with something else still stands.
By the way, I've proposed an edit to that KB article and removed the text that says the issue was fixed in SQL 2016 SP3. Remains to see if the edit is accepted. I included a reference to this thread in my commit comment.