Solved

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

Posted on 2006-07-12
8
354 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

863 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