1

I have a situation where a client is using software that writes to a SQL table. The program has been running fine with no issues prior to today.

Today, they began receiving errors inserting NULL into the ID field. Upon going in and looking at the tables, the column "ID" which is an identity field, was set to 'NO' for "is identity".

My question is, how could this change on its own? As far as the client and I am aware, nothing has been changed recently that would cause this.

Could this happen for another reason without manually going in and changing it?

| improve this question | |
  • 1
    I've seen this happen on older versions of SQL where a ALTER TABLE dbo.SomeTable ALTER COLUMN [ID] bigint not null will actually blow away the identity property of the column. Any chance this is 2008R2? Anything in the schema changes history report regarding that table (assuming the def trace hasn't rolled)? – Kris Gruttemeyer Nov 13 '18 at 21:27
  • Hello, thanks for the reply. They are using SQL Server 2012 currently, not 2008R2. Could you tell me where to look to view schema changes history report on that table? – Chris Nov 13 '18 at 21:55
  • 1
    You might check create_date and modify_date on sys.objects for the affected table. Just a shot in the dark, but I'd guess someone recreated the table (incorrectly) or altered the column (incorrectly). – AMtwo Nov 13 '18 at 22:02
  • 2
    You might be able to find evidence of this in the default trace, too, if it happened recently enough. See this question. – Aaron Bertrand♦ Nov 13 '18 at 22:18

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.