[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2006-07-12
8
Medium Priority
?
361 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 18

Accepted Solution

by:
SjoerdVerweij earned 2000 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
 

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

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 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