IDENTITY_INSERT in a multi-user environment
Posted on 2005-03-30
We are usign an IDENTITY column in a table and this table gets updated using a SPROC with IDENTITY_INSERT set to OFF - all is fine so far and our identity column gets auto incremented fine.
Meanwhile.... There is DTS package that every 30 seconds updates this table with data from a legacy database and needs to set IDENTITY_INSERT to ON as it passes in existing "unique" numbers.
Am I correct in assuming that IDENTITY_INSERT is database wide which would mean that while the DTS is doing it's thing if any regular updates come from the SPROC they will NOT get their column incremented?!?