Solved

Identity columns not being auto-generated, throwing an error on INSERT?

Posted on 2006-07-12
8
353 Views
Last Modified: 2008-01-09
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');
SELECT @@IDENTITY

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.

Any ideas?
0
Comment
Question by:avalys
8 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 17094580
Are you executing the insert into statement from a trigger??

if so pls check this link

http://www.devx.com/getHelpOn/10MinuteSolution/20550
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 17094594
Always use Scope_Identity() instead of @@Identity.
0
 

Author Comment

by:avalys
ID: 17094800
Ralmada:

No, this isn't being done from a trigger.  I found that article you mentioned earlier, but it is describing a different situation from what I'm doing.

SjoerdVerweij:

This error is occurring on the INSERT statement, before I ever execute "SELECT @@IDENTITY".  And the SQL Server documentation states that @@IDENTITY and Scope_Identity() are equivalent for my purposes.
0
 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 500 total points
ID: 17094987
That depends. If there is a trigger on the table that inserts into another table with an identity column, they are not  :-)

Something or someone is trying to specify a value for the identity column. There is no other way.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:avalys
ID: 17095043
That table is created and accessed only from within our application.  The customer doesn't even know about it, they interact with the application through a web interface.

The DDL and queries being executed are exactly those that I've posted.  There are no triggers.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 17095093
Either the identity property is removed, or someone is inserting a value.

Anyway, why not do

Create Table Seq(Value Int Not Null)
Go

Insert Into Seq(Value) Values(1000000)
Go

And then

Select Value From Seq With (HoldLock)
Update Seq Set Value = Value + 1
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17096956
How is the SQL submitted to the database? is it directly submitted as a string, or is it using some kind of data class?

Maybe the SQL is being altered on the way in by the driver?


You can run Profiler and capture the SQL being submitted.... you might be able to identify whats going in (though this may not be much help at a client site)
0
 

Author Comment

by:avalys
ID: 17312028
Well, the customer finally admitted that they had tried to move the database without telling us, and apparently failed to recreate the identity property on that column.

SjoerdVerwelj gets the points.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

759 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now