Identity columns not being auto-generated, throwing an error on INSERT?
Posted on 2006-07-12
I have an application that needs to be able to generate unique IDs. Because SQL Server does not have Oracle or DB2-style sequences, I'm emulating them using a table with an identity column, created as:
CREATE TABLE exSequence (keyid int identity, dummy varchar(2))
To generate a new ID, I execute:
INSERT INTO exSequence (dummy) VALUES ('aa');
This works okay 99% of the time. However, one of our customers is occasionally reporting an error during this process - the message received is as follows:
Cannot insert the value NULL into column 'keyid', table 'master.dbo.EXSEQUENCE'; column does not allow nulls. INSERT fails.
We are accessing the database through JDBC, but it does not seem to be a driver issue, as the stack trace shows the error coming from the server's response.
It seems like for some INSERTs, the server is forgetting that it should auto-generate a key for the identity column.
I do not know what version of SQL Server they're running, I'm trying to extract that information out of them now.