sql server msde: Could it be that one of your processes is inserting into the table using SET
IDENTITY INSERT ON command?
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


[quoted text, click to view]
Here's something that will make you scratch your head!

I have a table that has a UniqueIdentifier as its primary key, but also have
an autoincrement column (InvoiceNumber) that is not part of the primary key.

This table contains about 3000 records. But for an unknown reason
(UFOs???), the last 1500 rows or so have DUPLICATE autoincrement value! The
incrementation is done automatically by SQL Server (MSDE).

Here's an example of the values in the InvoiceNumber column:

1
2
3
4
4
5
5
6
6

Any idea what it could be? How can I fix/prevent this?

Thank you very much for your help!

hi Carl,
[quoted text, click to view]

Vyas already gave you an idea of the possible "guilty"...
the real problem is that you have to remember that the IDENTITY property of
a column does not directly imply uniqueness, as no constraint is enforced
with the column at all... it will be only given an autogenerated value at
insert time as long as SET IDENTITY_INSERT is not set
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_set-set_7zas.asp)..
you have to force your own constraint (or index) on the relevant column to
achieve complete uniqueness..
--
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtm http://italy.mvps.org
DbaMgr2k ver 0.14.0 - DbaMgr ver 0.59.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
--------- remove DMO to reply

Here's something that will make you scratch your head!

I have a table that has a UniqueIdentifier as its primary key, but also have
an autoincrement column (InvoiceNumber) that is not part of the primary key.

This table contains about 3000 records. But for an unknown reason
(UFOs???), the last 1500 rows or so have DUPLICATE autoincrement value! The
incrementation is done automatically by SQL Server (MSDE).

Here's an example of the values in the InvoiceNumber column:

1
2
3
4
4
5
5
6
6

Any idea what it could be? How can I fix/prevent this?

Thank you very much for your help!