Kim Neesgaard
asked on
Identity Specification has Changed by it self?
Hi all!
I have some SQL Server tables on a server and one of the tables uses identity specification as unique ID. When I insert a new record in the table, I use INSERT INTO table (fields) VALUES (some values) and then the system inserts the next ID in the table. It has worked very well for a long time.
'Suddenly' one of the uses get an error that says: column does not allow nulls. INSERT fails. After investigation of the table in question, I discover that identity specification is set to 'no'. I am very sure that I have not changed the value to 'no' and no other has access to the table.
My question is: is it possible that this value can change 'by itself' or could there be circumstaces where the value is changed so ti appear that it has chenged 'by itself'?
After I changed to 'yes', no problems exist.
Best regards
Kim Neesgaard
I have some SQL Server tables on a server and one of the tables uses identity specification as unique ID. When I insert a new record in the table, I use INSERT INTO table (fields) VALUES (some values) and then the system inserts the next ID in the table. It has worked very well for a long time.
'Suddenly' one of the uses get an error that says: column does not allow nulls. INSERT fails. After investigation of the table in question, I discover that identity specification is set to 'no'. I am very sure that I have not changed the value to 'no' and no other has access to the table.
My question is: is it possible that this value can change 'by itself' or could there be circumstaces where the value is changed so ti appear that it has chenged 'by itself'?
After I changed to 'yes', no problems exist.
Best regards
Kim Neesgaard
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Anthony!
I was allowed to change identity specification from no to yes but perhaps a rebuild was made behind the scenes. In the options 'prevent saving changes that require table re-creation' is not ticked.
I was allowed to change identity specification from no to yes but perhaps a rebuild was made behind the scenes. In the options 'prevent saving changes that require table re-creation' is not ticked.
I was allowed to change identity specification from no to yes but perhaps a rebuild was made behind the scenes.
You are right, behind the scene SSMS is rebuilding the table. You can see exactly what I mean by "removing" the attribute in the Table Designer but instead of clicking Save, select the icon that reads "Generate Change Script" (it is also under the Table Designer menu.
You are right, behind the scene SSMS is rebuilding the table. You can see exactly what I mean by "removing" the attribute in the Table Designer but instead of clicking Save, select the icon that reads "Generate Change Script" (it is also under the Table Designer menu.
ASKER
I have just checked it and it is very visible under Generate Change Script - I didn't know this function. Thank you for elaborate the question!
ASKER
Thank you for your answer! Actually I can see (and recall) that there has been some copy activities for exactly this table so your first guess seems likely. I certainly not hope for you last suggestion...!
Best regards
Kim Neesgaard