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)
In Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
it throws following exception:
Cannot insert explicit value for identity column in table
Cannot insert explicit value for identity column in table
'#TempTable' when IDENTITY_INSERT is set to OFF.
Sample Code:
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.
Looks like this bug exist from CTP 2.3
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
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