Primary Key Violations (SQL 6.5) 
Author Message
 Primary Key Violations (SQL 6.5)

We have 20 workstations connecting to a SQL Server database.  All keys are
'autonumbered' (the tables are compatible with a scaled-down version of the
system in Access 7.0).  We access the SQL Server from Visual C++ 4.2 built
recordsets with AddNew() and Update() sequences. The Server is running NT
4.0 Server, the workstations are NT 4.0 workstations.

The Problem:

About once a day we get a random "Primary Key Violation", which brings the
SQL Server down.  We have no explanation for this.  When we use clustered
indexes the problem is much more frequent than when using non-clustered
indexes.  But we think the problem is still there, just more hidden.

Any ideas, hints or follow-on questions would be appreciated.

Norbert.
Controltec.



Sun, 03 Oct 1999 03:00:00 GMT
 Primary Key Violations (SQL 6.5)

We are doing something similar. Basically we have tables where the Primary
Key column is an identity (a sequential number generated by SQL server) but
often we get a primary key violation during a simple insert. I'm still
trying to figure out what is causing the problem.

By the way, did you use ErWin to generate the SQL tables?

Please let me know if you find the answer.

--
Mitchel Burns
The Information Management Group



Tue, 05 Oct 1999 03:00:00 GMT
 Primary Key Violations (SQL 6.5)

I've read that SQLServer 6.5 has a bug where the next IDENTITY value will
be occassionally forgotten after doing a restore, transfer, or simply
starting up the server.  Supposedly running DBCC CHECKIDENT(tablename) on
each table fixes the problem.  Try creating a stored procedure that
automatically executes at startup and runs DBCC for each table containing
an IDENTITY column.  Also remember to run it after any transfer or restore.

I can't verify that this works 100%, but I haven't had any problems with it
yet.  However, my database isn't yet in production.

Please let us know how it works!

Jeff
--
Note:  Remove the .NOSPAM from my email address to reply!



: We have 20 workstations connecting to a SQL Server database.  All keys
are
: 'autonumbered' (the tables are compatible with a scaled-down version of
the
: system in Access 7.0).  We access the SQL Server from Visual C++ 4.2
built
: recordsets with AddNew() and Update() sequences. The Server is running NT
: 4.0 Server, the workstations are NT 4.0 workstations.
:
: The Problem:
:
: About once a day we get a random "Primary Key Violation", which brings
the
: SQL Server down.  We have no explanation for this.  When we use clustered
: indexes the problem is much more frequent than when using non-clustered
: indexes.  But we think the problem is still there, just more hidden.
:
: Any ideas, hints or follow-on questions would be appreciated.
:
: Norbert.
: Controltec.
:



Tue, 05 Oct 1999 03:00:00 GMT
 Primary Key Violations (SQL 6.5)

Create a stored procedure that automatically runs at startup.  In it run
DBCC CHECKIDENT (tablename) for each table with an identity column.
--
Note:  Remove the .NOSPAM from my email address to reply!



: We are doing something similar. Basically we have tables where the
Primary
: Key column is an identity (a sequential number generated by SQL server)
but
: often we get a primary key violation during a simple insert. I'm still
: trying to figure out what is causing the problem.
:
: By the way, did you use ErWin to generate the SQL tables?
:
: Please let me know if you find the answer.
:
:
: --
: Mitchel Burns
: The Information Management Group

:



Mon, 11 Oct 1999 03:00:00 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. SQL 6.5 MSDB Violation of Primary Key pk_backup_id

2. SQL Server 6.5 Violation of PRIMARY KEY

3. IDENTITY (MSSQL 6.5) - Primary Key violation

4. 23000: [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint

5. INSERT INTO TABLE KEYED ONLY ON IDENTITY COLUMN RESULTS IN KEY VIOLATION (6.5)

6. Violation of PRIMARY KEY constraint Cannot insert duplicate key in object

7. Sql 6.5 Replica without primary key

8. Primary Key Violation - Please HELP !!!!!

9. Primary Key Violation..HowCome

10. Violation of primary key constraint

11. Transactional Replication, Violation of PRIMARY KEY


 
Powered by phpBB® Forum Software