Solved

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

Posted on 2006-07-12
8
357 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

 
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
 

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 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