We are migrating to SQL Server 2019 RTM version and noticed that one of our stored procedures that uses SET IDENTITY_INSERT ON/OFF statements are failing which works properly in SQL Server 2017 and earlier.
Even changing the compatibility level to SQL Server 2017 does not work the same way in SQL Server 2019. This looks like a bug/behavior change that is not documented anywhere.
Anyone encountering similar issues or know if it is a reported problem? I tried searching for it but another person who reported the issue was a combination of ODBC and SQL Server 2019 CTP versions.
We have lots of customers running earlier versions of our product on SQL Server 2017 and earlier with this stored procedure that we cannot afford to change but still want to migrate to SQL Server 2019 and now this has become a show stopper for our SQL Server 2019 migrations.
Here is code snippet that fails in SQL Server 2019 but works fine in SQL Server 2017 and before
How to reproduce the behavior.
CREATE PROC proc_inner
AS
BEGIN
SET IDENTITY_INSERT [#TMP_MESSAGE] ON;
INSERT INTO [#TMP_MESSAGE] (DCORP, ENTITYKEY, SEQNO, MESSAGE)
SELECT
'test', 1, 1, 'bdkfsjk';
SET IDENTITY_INSERT #TMP_MESSAGE OFF;
END;
GO
CREATE PROC proc_outer
AS
BEGIN
IF OBJECT_ID('TEMPDB..[#TMP_MESSAGE]') IS NULL
BEGIN
CREATE TABLE [#TMP_MESSAGE]
(
DCORP CHAR(10),
ENTITYKEY INT,
SEQNO INT IDENTITY(1, 1),
MESSAGE VARCHAR(8000)
);
END;
EXEC proc_inner;
SELECT *
FROM #TMP_MESSAGE;
END;
GO
EXEC proc_outer; -- this statement fails on SQL 2019
In SQL Server 2019 we get this error:
Msg 544, Level 16, State 1, Procedure proc_inner, Line 5 [Batch Start Line 36]
Cannot insert explicit value for identity column in table '#TMP_MESSAGE' when IDENTITY_INSERT is set to OFF. (0 rows affected)
In SQL Server 2017 and earlier, the same statement works properly.
