Duplicate Key with Identity fields

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)

Duplicate Key with Identity fields

Author

Topic

pjn
Starting Member

27 Posts

Posted - 2004-06-14 : 10:12:58

Hi

I'm going round in circles here! I have a table where the PK is an IDENTITY column. I then have an SP that inserts a record into the table, and returns the PK as an output parameter

Things seem to work okay for starters, then all of a sudden I start getting duplicate PK errors, even though the DBCC CHECKIDENT is showing the correct values.


Violation of PRIMARY KEY constraint 'PK_uv_val_out'. Cannot insert duplicate key in object 'uv_val_out'.

Can anyone see what is wrong? To replicate it I do the following:

Run the stored proc quite a few times using QA.
Then SELECT * FROM uv_val_out just to check it all.
Then run the stored proc again... and the error crops up.

Table:


CREATE TABLE dbo.uv_val_out
(
    PK_uv_val_out_ID    int                 NOT NULL IDENTITY(1,1),
    uv_server           sysname             NOT NULL DEFAULT @@SERVERNAME,
    uv_comp             char(3)             NULL,
    uv_userid           varchar(10)         NOT NULL,
    uv_datetime         datetime            NOT NULL DEFAULT GETDATE(),
    uv_till             char(3)             NULL,
    uv_trad             varchar(8)          NULL,
    uv_serv             varchar(8)          NULL,
    uv_cardid           varchar(25)         NULL,
    uv_cardbal          decimal(10,2)       NOT NULL DEFAULT 0,
    uv_tok1             decimal(8,2)        NOT NULL DEFAULT 0,
    uv_tok2             decimal(8,2)        NOT NULL DEFAULT 0,
    uv_tok3             decimal(8,2)        NOT NULL DEFAULT 0,
    uv_tok4             decimal(8,2)        NOT NULL DEFAULT 0,
    uv_dayly            decimal(8,2)        NOT NULL DEFAULT 0,
    uv_weekly           decimal(8,2)        NOT NULL DEFAULT 0,
    uv_mhtok            decimal(8,2)        NOT NULL DEFAULT 0,
    uv_lstrev           decimal(8,2)        NOT NULL DEFAULT 0,
    uv_loyalty          int                 NOT NULL DEFAULT 0,
    uv_klix             decimal(10,2)       NOT NULL DEFAULT 0,
    uv_meal             varchar(6)          NULL,
    uv_meals            int                 NOT NULL DEFAULT 0,
    uv_meale            int                 NOT NULL DEFAULT 0,
    uv_wktok            decimal(7,2)        NOT NULL DEFAULT 0,
    uv_sent             datetime            NULL
)
ALTER TABLE dbo.uv_val_out
    ADD CONSTRAINT PK_uv_val_out
            PRIMARY KEY CLUSTERED (PK_uv_val_out_ID)
            WITH FILLFACTOR = 80

Then the stored proc:


CREATE PROCEDURE dbo.Globalsp_UV_VAL_OUT_Insert
(
    @TMPUserID          varchar(10)     = NULL,
    @PK_uv_val_out_ID   int             = NULL  OUTPUT,
    @uv_server          sysname         = NULL,
    @uv_comp            char(3)         = NULL,
    @uv_userid          varchar(10)     = NULL,
    @uv_datetime        datetime        = NULL,
    @uv_till            char(3)         = NULL,
    @uv_trad            varchar(8)      = NULL,
    @uv_serv            varchar(8)      = NULL,
    @uv_cardid          varchar(25)     = NULL,
    @uv_cardbal         decimal(10, 2)  = NULL,
    @uv_tok1            decimal(8, 2)   = NULL,
    @uv_tok2            decimal(8, 2)   = NULL,
    @uv_tok3            decimal(8, 2)   = NULL,
    @uv_tok4            decimal(8, 2)   = NULL,
    @uv_dayly           decimal(8, 2)   = NULL,
    @uv_weekly          decimal(8, 2)   = NULL,
    @uv_mhtok           decimal(8, 2)   = NULL,
    @uv_lstrev          decimal(8, 2)   = NULL,
    @uv_loyalty         int             = NULL,
    @uv_klix            decimal(10, 2)  = NULL,
    @uv_meal            varchar(6)      = NULL,
    @uv_meals           int             = NULL,
    @uv_meale           int             = NULL,
    @uv_wktok           decimal(7, 2)   = NULL,
    @uv_sent            datetime        = NULL
)
AS
    SET NOCOUNT ON

    DECLARE @LastError  int

    INSERT INTO dbo.uv_val_out
    (
        uv_server,
        uv_comp,
        uv_userid,
        uv_datetime,
        uv_till,
        uv_trad,
        uv_serv,
        uv_cardid,
        uv_cardbal,
        uv_tok1,
        uv_tok2,
        uv_tok3,
        uv_tok4,
        uv_dayly,
        uv_weekly,
        uv_mhtok,
        uv_lstrev,
        uv_loyalty,
        uv_klix,
        uv_meal,
        uv_meals,
        uv_meale,
        uv_wktok,
        uv_sent
    )
    VALUES
    (
        COALESCE(@uv_server, @@servername),
        @uv_comp,
        @uv_userid,
        COALESCE(@uv_datetime, getdate()),
        @uv_till,
        @uv_trad,
        @uv_serv,
        @uv_cardid,
        COALESCE(@uv_cardbal, 0),
        COALESCE(@uv_tok1, 0),
        COALESCE(@uv_tok2, 0),
        COALESCE(@uv_tok3, 0),
        COALESCE(@uv_tok4, 0),
        COALESCE(@uv_dayly, 0),
        COALESCE(@uv_weekly, 0),
        COALESCE(@uv_mhtok, 0),
        COALESCE(@uv_lstrev, 0),
        COALESCE(@uv_loyalty, 0),
        COALESCE(@uv_klix, 0),
        @uv_meal,
        COALESCE(@uv_meals, 0),
        COALESCE(@uv_meale, 0),
        COALESCE(@uv_wktok, 0),
        @uv_sent
    )

    SET @LastError = @@ERROR
    IF @LastError = 0
        SET @PK_uv_val_out_ID = SCOPE_IDENTITY()
    ELSE
        SET @PK_uv_val_out_ID = 0

    RETURN @LastError
GO

And finally the code to run the stored proc:


DECLARE @RC int
DECLARE @PK_uv_val_out_ID int
DECLARE @uv_comp char(3)
DECLARE @uv_userid varchar(10)
DECLARE @uv_datetime datetime
DECLARE @uv_cardbal decimal(10,2)

SET @uv_comp = '001'
SET @uv_userid = '61034'
SET @uv_datetime = GETDATE()
SET @uv_cardbal = -12.34

EXEC @RC = dbo.Globalsp_UV_VAL_OUT_Insert @PK_uv_val_out_ID = @PK_uv_val_out_ID OUTPUT, @uv_comp = @uv_comp, @uv_userid = @uv_userid, @uv_datetime = @uv_datetime, @uv_cardbal = @uv_cardbal

PRINT @PK_uv_val_out_id

I can't see where I'm going wrong - please help!

I should mention that I'm using MSDE 2000 SP3a

JasonGoff
Posting Yak Master

158 Posts

Posted - 2004-06-14 : 10:19:47

Ran this on SQL2000. Over 100 executions of SP, running the SELECT you specified every now and then to return the resultset. Didn't have a problem !

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-14 : 10:32:59

Like Jason, I ran this on SQL2000 (in a loop, running it a total of 100,000 times!) and no problems encountered. Sorry :(

Raymond

pjn
Starting Member

27 Posts

Posted - 2004-06-14 : 10:36:28

Thanks - in a way that's good to know because I thought I'd mistyped something and the answer was staring me in the face.

I've even tried totally uninstalling MSDE and running all the scripts again, but the same thing happens.

If anyone else has the time, could they give it a go with MSDE to see what happens?

pjn
Starting Member

27 Posts

Posted - 2004-06-14 : 10:53:29

Actually I've just noticed something.


Checking identity information: current identity value '12', current column value '100'.

I ran the code 100 times (hence identity column of 100) but then the value seems to have reset itself - it's now showing as 12 (which increments each time I try running it again)

I am the only person accessing this database, and nowhere am I resetting the value to 0. Can't understand why it has reset.

I then did a reseed to set back to 100, successfully executed it another 50 times, did a SELECT *, and it broke again.

pjn
Starting Member

27 Posts

Posted - 2004-06-14 : 11:04:13

Oh b*llocks! I think I know why.

I had the following code in QA:


SELECT * FROM uv_val_out
SELECT * FROM uv_val_in

RETURN

DELETE FROM uv_val_out
DELETE FROM uv_val_in
GO

DBCC CHECKIDENT ('uv_val_out', 'reseed', 0)
DBCC CHECKIDENT ('uv_val_in', 'reseed', 0)
GO

When I was executing this, I was assuming that it would stop running at the RETURN statement. However, it also appears to be running the RESEED commands at the end. BUT (!) what I can't understand is why the DELETE statements aren't getting executed as well - all the data is still in the tables.

In future, I'll just highlight the lines I want to run instead of using RETURN. (But please feel free to explain why the DELETE statements were ignored, whilst the RESEED commands were running!)

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-14 : 11:07:55

That is essentially two batches - separated by the GO statements. The RETURN terminates the first batch and therefore the DELETEs aren't executed.

Raymond

pjn
Starting Member

27 Posts

Posted - 2004-06-14 : 11:13:42

I see - thanks Raymond - it didn't realise it worked like that. I won't forget that one in a hurry - I've wasted 2 days on this

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-14 : 11:15:20

We live and learn ...

Raymond

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources