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...
> 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
>--Original Message--
>Julie
> 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...
>> 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
>
>.
>|||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...
> 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
>
> >--Original Message--
> >Julie
> > 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...
> >> 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
> >
> >
> >.
> >|||Hi Uri,
This has been sorted.
One of out developmers did something naughty. He has been
chastised ;)
J
>--Original Message--
>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...
>> 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
>>
>> >--Original Message--
>> >Julie
>> > 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...
>> >> 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
>> >
>> >
>> >.
>> >
>
>.
>sql