We have this piece of (anonymyzed code)

create table #SomeTmpTable
  [RowNumber]         integer identity,
  [Value]             integer,
  primary key (RowNumber)  

insert into #SomeTmpTable (RowNumber, Value)
execute SomeSchema.GetResultSet 
    @ParamA = 9999, 
    @ParamB = 'Alpha', 
    @RowCount = 5

GetresultSet is a procedure that does something and most important returns a two column result set.

Issue: Even though RowNumber is an identity column in #SomeTmpTable, the insert sometimes works.

Sometime definition: DEV computer (SQL2016 - works) (edit: doesnt work on SQL2012) Q&A computer - fails - SQL 2012

replacing execute with a select - fails...

Issue in code was easy to fix per se (remove identity, makes no sense having it there), but the question here is:

Why did it work on DEV PC?

Thank you

EDIT: So it seems for some reason, on MS SQL 2016, inserting into identity column from a stored procedure's result set is OK. Trying to dig up some official info on it

  • Are you using the latest service pack / CU on 2016? Maybe there was a bug that was later fixed. Another guess: is there any chance that you are setting IDENTITY_INSERT to ON on a Dev machine and forgetting to set it to OFF? – Vladimir Baranov Oct 13 '17 at 12:16
  • @VladimirBaranov Version 13.0.1742.0 IDENTITY_INSERT HIGHLY unlikely, its an temp table thats created as needed, reproducing the issue, insert from procedure works, from select not. If it would be identity insert set on, insert from select would work too – Vladislav Zalesak Oct 13 '17 at 12:30
  • I was able to reproduce this behavior on SQL Server 2014 (works from SP, fails on SELECT), but not SQL Server 2012 (fails on both SP and SELECT). The behavior seems to be the same whether you're using a temp table or real table with IDENTITY - I can't find any official document that addresses this behavior change though - still looking - I will update, if I find anything. – Scott Hodgin Oct 13 '17 at 14:19
  • @ScottHodgin, I also could reproduce it on SQL Server 2014 Express. I think it is a bug. Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64) May 27 2016 15:33:17 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1). I tried to set IDENTITY_INSERT to ON and OFF explicitly and it didn't change behaviour of INSERT ... EXEC. INSERT ... EXEC worked without errors when stored procedure returns a column for the IDENTITY column and when stored procedure doesn't return a column for the IDENTITY column. – Vladimir Baranov Oct 14 '17 at 5:19
  • Reproduced on Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) - 13.0.4446.0 (X64) Jul 16 2017 18:08:49 Copyright (c) Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 6.3 <X64> (Build 15063: ) (Hypervisor) – Tapakah Ua Oct 25 '17 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.