[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 362
  • Last Modified:

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

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
avalys
Asked:
avalys
1 Solution
 
ralmadaCommented:
Are you executing the insert into statement from a trigger??

if so pls check this link

http://www.devx.com/getHelpOn/10MinuteSolution/20550
0
 
SjoerdVerweijCommented:
Always use Scope_Identity() instead of @@Identity.
0
 
avalysAuthor Commented:
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
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
SjoerdVerweijCommented:
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
 
avalysAuthor Commented:
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
 
SjoerdVerweijCommented:
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
 
nmcdermaidCommented:
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
 
avalysAuthor Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now