Pages

Friday, November 15, 2019

Bug in SQL Server 2019: SET IDENTITY_INSERT ON a temp table in nested/inner SP/scope

Consider the following use case:

You have 2 stored procedures, one is calling another.
In the outer SP you define a temp table with an Identity field.
And in the second SP you do insert to this temp table with
SET IDENTITY_INSERT ON option.

In Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64) 
it throws following exception:

Cannot insert explicit value for identity column in table 
'#TempTable' when IDENTITY_INSERT is set to OFF.
Sample Code:


CREATE PROCEDURE dbo.uspTestIdenityInsert_Outer
AS
SET NOCOUNT ON


-- Create temp table
CREATE TABLE #TempTable
(
RowID INT IDENTITY(1,1) NOT NULL,
FName VARCHAR(50) NOT NULL
)

-- exec inner SP that inserts data to the temp table.
EXEC dbo.uspTestIdenityInsert_Inner
GO


CREATE PROCEDURE dbo.uspTestIdenityInsert_Inner
AS
SET NOCOUNT ON


SET IDENTITY_INSERT #TempTable ON

INSERT INTO #TempTable(RowID,FName)
SELECT 1 as RowID,'Dan' as FName
GO


--------------------------------------------------------------------------
EXEC dbo.uspTestIdenityInsert_Outer

Msg 544, Level 16, State 1, Procedure dbo.uspTestIdenityInsert_Inner, Line 8 [Batch Start Line 26]
Cannot insert explicit value for identity column in table '#TempTable' when IDENTITY_INSERT is set to OFF.



This bug is due to the implementation of the new feature
“Reduced recompilations for workloads using temporary tables across multiple scopes” 
that was introduced in SQL Server 2019

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/04/reduced-recompilations-for-workloads-using-temporary-tables-across-multiple-scopes/
The workaround is to disable this new feature by using trace flags 11036, 11048
that can be added as SQL startup parameters

No comments:

Post a Comment