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?
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