identity fields as primary key in Server 7.0

[Brian White]

Most of the tables in my database are implemented with IDENTITY columns as the primary key.
When an INSERT from my application is attempted, sometimes I get an error returned stating that insert
cannot be done because of duplicate key value. If I try the INSERT again, sometimes it works(??). Of course,
DBCC CHECKIDENT resets the identity value if trying the INSERT again doesn't work. Then sometime a little later, the problem happens again.
Is there anything I can do other than placing into my application code the execution of dbcc checkident
anytime I want to do an insert to prevent the error? By the way, DBCC CHECKDB revealed no problems.

HELP!

[Brian J. Smith]

Are there any indexes on these tables that prevent duplicate values? The duplicates might not be caused by the identity, but indexes with "no duplicates" checked.




------------
Brian White at 5/13/99 3:33:55 PM

Most of the tables in my database are implemented with IDENTITY columns as the primary key.
When an INSERT from my application is attempted, sometimes I get an error returned stating that insert
cannot be done because of duplicate key value. If I try the INSERT again, sometimes it works(??). Of course,
DBCC CHECKIDENT resets the identity value if trying the INSERT again doesn't work. Then sometime a little later, the problem happens again.
Is there anything I can do other than placing into my application code the execution of dbcc checkident
anytime I want to do an insert to prevent the error? By the way, DBCC CHECKDB revealed no problems.

HELP!

[Brian White]

No, the only unique index is the primary key.


------------
Brian J. Smith at 5/13/99 6:10:00 PM

Are there any indexes on these tables that prevent duplicate values? The duplicates might not be caused by the identity, but indexes with "no duplicates" checked.




------------
Brian White at 5/13/99 3:33:55 PM

Most of the tables in my database are implemented with IDENTITY columns as the primary key.
When an INSERT from my application is attempted, sometimes I get an error returned stating that insert
cannot be done because of duplicate key value. If I try the INSERT again, sometimes it works(??). Of course,
DBCC CHECKIDENT resets the identity value if trying the INSERT again doesn't work. Then sometime a little later, the problem happens again.
Is there anything I can do other than placing into my application code the execution of dbcc checkident
anytime I want to do an insert to prevent the error? By the way, DBCC CHECKDB revealed no problems.

HELP!

[Barb Dornbrook]

Brian we also use identity columns as primary keys and experienced the same problem. I run the following stored procedure in start
up and have not had any problems since. Use the sp_procoption to put this in startup. You will then need to stop and start your
services.

/****** Object: Stored Procedure dbo.Check_Identity_Columns Script Date: 3/13/99 7:26:38 PM ******/
create procedure Check_Identity_Columns
as
declare @db varchar(30),
@tbl varchar(30),
@sql varchar(255),
@show varchar(255)

declare curDB insensitive cursor
for select name from sysdatabases
for read only

open curDB
fetch next from curDB into @db
while @@fetch_status = 0
begin
execute("declare curTBL insensitive cursor for select so.name from "+@db+"..sysobjects so join "+@db+"..syscolumns sc on sc.id = so.id where sc.status = 0x80 for read only&#34
open curTBL
fetch next from curTBL into @tbl
while @@fetch_status = 0
begin
select @sql = "use "+@db+char(10)+"dbcc checkident("+@tbl+&#34 WITH NO_INFOMSGS"
select @show = "Checking Identity Column for Table: "+@db+".."+@tbl+char(10)+replicate(&#3 4;-",70)
print @show
execute(@sql)
fetch next from curTBL into @tbl
end
close curTBL
deallocate curTBL
fetch next from curDB into @db
end
close curDB
deallocate curDB
exec sp_procoption N'Check_Identity_Columns'



------------
Brian White at 5/13/99 3:33:55 PM

Most of the tables in my database are implemented with IDENTITY columns as the primary key.
When an INSERT from my application is attempted, sometimes I get an error returned stating that insert
cannot be done because of duplicate key value. If I try the INSERT again, sometimes it works(??). Of course,
DBCC CHECKIDENT resets the identity value if trying the INSERT again doesn't work. Then sometime a little later, the problem happens again.
Is there anything I can do other than placing into my application code the execution of dbcc checkident
anytime I want to do an insert to prevent the error? By the way, DBCC CHECKDB revealed no problems.

HELP!