Currently we are facing the issue only when we are executing a stored procedure keeping the Include Actual Execution Plan - ON. Otherwise the stored procedure is executing fine and returning results as expected. What would be the reason for this kind of behavior?
I have already went through this links but the error is different here as it only occurs when we have kept the Include Actual Execution Plan - ON. Link1 Link2
Sample code (PROC1) -
CREATE PROCEDURE PROC1 (blah blah blah)
AS
BEGIN
BEGIN TRY
-------------
code
--------------
-----issue code-----
INSERT INTO #temptable (col1,col2,.....)
EXECUTE PROC2
-------------
code
--------------
END TRY
BEGIN CATCH
---------
RAISERROR(............);
END CATCH
END
Sample code (PROC2) -
CREATE PROCEDURE PROC2
BEGIN
BEGIN TRY
---------------
code
---------------
SELECT COL1,COL2,COL3,..... FROM #innersptemptable
END TRY
BEGIN CATCH
--------------------
RAISERROR();
--------------------
END CATCH
END
Note: PROC2 returns exact same number of columns which we have taken care while inserting into #temptable
Do let me know if any further information is required.
Environment -
Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Feb 20 2014 20:04:26
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Edit1: When the error occurs in PROC1 and it is captured, it is noted that the ERROR_PROCEDURE() return the value of PROC2 but again PROC2 runs fine and gives results as expected with and without the Include Actual Execution Plan kept ON.
Edit2: When we replaced local temp table with global temp table (the temp table I am talking about is used to pass the result set from PROC2) inside PROC2 then the execution of PROC1 happened successfully.
Edit3: When we removed the TRY-CATCH block from the inner sp (PROC2) and executed the PROC1 keeping Include Actual Execution Plan - ON no errors were reported and execution was completed successfully.
