Strange error with an "identity" column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Strange error with an "identity" column

Author

Topic

avalys
Starting Member

2 Posts

Posted - 2006-07-12 : 16:43:15

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.

Any ideas you could offer would be appreciated. I do not know what version of SQL Server they're running, I'm trying to extract that information out of them now.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-12 : 16:46:57

Could you post the DDL (CREATE TABLE statement) for exSequence?

Tara Kizer
aka tduggan

avalys
Starting Member

2 Posts

Posted - 2006-07-12 : 16:48:06

quote:
Originally posted by tkizer

Could you post the DDL (CREATE TABLE statement) for exSequence?

Tara Kizer
aka tduggan

It's there, right after the first paragraph.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-07-12 : 17:02:01

Oops, missed it! Sorry about that.

If you are getting that error, then someone must be changing the DDL of the table. I would start running SQL Profiler to capture exactly what is going on when the error occurs.

Tara Kizer
aka tduggan

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2006-07-13 : 08:03:20

also, use SCOPE_IDENTITY() instead of @@IDENTITY

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

nr
SQLTeam MVY

12543 Posts

Posted - 2006-07-13 : 08:17:12

For that to happen I suspect someone is trying to insert an explicit identity value.
If all you want is a sequence number then you can generatethat by having a table
create table x (i int)
insert x select 0

then when you want a new sequence value
declare @i int
update x
set @i = i + 1, i = i + 1
select @i

This also means that you don't get gaps in the allocated values (but that shouldn't matter).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources