Odd Primary Key Error

Dear All,
I have been getting Primary key violation errors, however
I am a bit confused why. Here is the reason
We have a table with the following structure with the ID
field as the primary key.
CREATE TABLE [dbo].[tblTest] (
[ID] [int] IDENTITY (8, 20) NOT NULL , ...
and
ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
We then use a store procedure to insert items into the
table. The store procedure does not access the ID field,
but it does return it after insert.
Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
commands I have found the next key to be generated is
correct.
However we are starting to get intermediate primary key
violation errors.
Any pointers please.
JJulie
Did you try in the bottom of the sp adding return @.@.identity and the to
carry out on the client side?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...

quote:

---

> Dear All,
> I have been getting Primary key violation errors, however
> I am a bit confused why. Here is the reason
> We have a table with the following structure with the ID
> field as the primary key.
> CREATE TABLE [dbo].[tblTest] (
> [ID] [int] IDENTITY (8, 20) NOT NULL , ...
> and
> ALTER TABLE [dbo].[tblTest] WITH NOCHECK ADD
> CONSTRAINT [PK_tblTest] PRIMARY KEY CLUSTERED
> (
> [ID]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> We then use a store procedure to insert items into the
> table. The store procedure does not access the ID field,
> but it does return it after insert.
> Using the IDENT_SEED, IDENT_INCR and IDENT_CURRENT
> commands I have found the next key to be generated is
> correct.
> However we are starting to get intermediate primary key
> violation errors.
> Any pointers please.
> J

---

|||Almost at the end of every insert store procedure we have
a 'RETURN scope_identity()' command.
This is filtered back to the VB app though ado.
J

quote:

---

>--Original Message--
>Julie
> Did you try in the bottom of the sp adding return

---

@.@.identity and the to

quote:

---

>carry out on the client side?
>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:0a3601c3db48$8cabbf60$a301280a@.phx.gbl...
however[QUOTE]
>
>.
>

---

|||Julie
Look , IDENTITY property does not guarantee sequence of insertion. There may
be gaps.
Can you provide sample data + code of your sp to reproduce your problem?
"Julie" <anonymous@.discussions.microsoft.com> wrote in message
news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...[QUOTE]
> Almost at the end of every insert store procedure we have
> a 'RETURN scope_identity()' command.
> This is filtered back to the VB app though ado.
> J
>
> @.@.identity and the to
> message
> however|||Hi Uri,
This has been sorted.
One of out developmers did something naughty. He has been
chastised ;)
J

quote:

---

>--Original Message--
>Julie
>Look , IDENTITY property does not guarantee sequence of

---

insertion. There may

quote:

---

>be gaps.
>Can you provide sample data + code of your sp to

---

reproduce your problem?

quote:

---

>
>
>"Julie" <anonymous@.discussions.microsoft.com> wrote in

---

message

quote:

---

>news:0adf01c3db51$1b72ad00$a101280a@.phx.gbl...
have[QUOTE]
the ID[QUOTE]
the[QUOTE]
field,[QUOTE]
key[QUOTE]
>
>.
>

---