Unusual -- Violation of PRIMARY KEY constraint ERROR
-
Hi All
I am working on SQL Server 2000 using C#.NET 2003 and VB6.
I have 1 Stored Procedure which takes input parameters and does an insert to a table. This stored Procedure is called by the 2 applications mentioned above.
Here is the error which i get, which occures anytime which is not certain. I mean one fine day this error will start and No more records can be inserted into the Table unless last record is deleted.
-2147217900
[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_Transactions'. Cannot insert duplicate key in object 'Transactions'.
Couldnt understand what starts this problem
BELOW is the Stored Procedure (Sorry for the huge detail)
CREATE PROCEDURE TransactionRegister
@Transaction_Date datetime,
@Transaction_Time varchar(20),
@ItemShrtName varchar(30),
...[SOME MORE variables]
@Bill_AmountTotalFC float,
@FuelTransTerminalID varchar(50),
@TransCurrencyCode varchar (5),
@TransCurrencyExRt float,
@Identity int OUT
AS
BEGIN
Declare @costprice float
Declare @batchno varchar(20)
Select TOP 1 @costprice=PackPrice, @batchno=BatchNumber
from StockBatches
Where ItemCode=@ItemCode AND StoreCode=@StoreCode
ORDER BY AddTime Desc
Insert Into Transactions
(
Transaction_Date,
Transaction_Time,
ItemShrtName,
ItemCode,
...[Some More columns]
Bill_AmountTotalFC,
TransCurrencyCode,
TransCurrencyExRt,
Transaction_Code,
ItmCostPrice,
ItmBatchNoRef)
Values
(@Transaction_Date ,
@Transaction_Time,
@ItemShrtName,
@ItemCode,
...[SOME VALUES]
@Bill_AmountTotalFC,
@TransCurrencyCode,
@TransCurrencyExRt,
'TMP'+@storecode + RTrim(CAST(@poscode as char)), @costprice, @batchno)
SET @Identity = SCOPE_IDENTITY()
DECLARE @Temp varchar(50)
set @Temp= 'TRN'+@StoreCode+RTRIM(CAST(@PosCode AS char))+RTrim(CAST(@Identity AS char))
UPDATE Transactions SET Transaction_Code=@Temp WHERE Transaction_No=@Identity
END
GO
Kindest Regards,Rakesh Salian
-
I'm presuming that it is the identity column on the Transactions table that is the primary key in question?
When you "no more records can be inserted," do you mean inserted by any means, or inserted via this stored procedure?
-
Yes the Primary key is in the Transactions Table and is an Auto Number(Identity column). And it is not possible to enter any more records via any means unless i delete the last record in the table.
Kindest Regards,Rakesh Salian
-
Is it possible that there's an insert into the transaction table from somewhere else, with identity_insert switched on?
It almost sounds like someone's inserting the 'next' transaction and manually assigning the pk value and when SQL generates the next identity value it's already in the table.
It might be worth running profiler for a while, see if you can see what starts the problem.
Also, check to make sure you've not got any database corruption. (DBCC CHECKDB)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
When this occurs, use
DBCC
CHECKIDENT ('table_name', NORESEED)
to see if a value higher than the current identity value has been inserted, as suggested by GilaMonster above.
The results of this vary, so you should ignore the standard message and just look at the numbers. If the first number is less than the second, a record has been inserted in this way and your identity value is out of sync with the data values.
If you need to correct the problem again, you can run
DBCC CHECKIDENT ('table_name', RESEED)
But of course this is only a firefighting tactic and you need to search all app code including TSQL code for use of SET IDENTITY INSERT, then check whether it is using only recycled values from deleted records, and if not change it to use a normal identity insert and SCOPE_IDENTITY().
There is one circumstance in which this is not an adequate solution - if apps need to insert multiple records in a single INSERT statement and still know what the generated keys will be. In this case, you need to get rid of the identity property on the column and create a one-row, one column table containing an integer representing the last, or next, available PK value. It doesn't matter which as long as you are clear about it!
You then need to write a stored proc whih people can use to read and update this value (i.e.table) in transaction (you may as well force TABLOCKX) to reserve a range of keys before using that range in an INSERT.
This is a bit more wasteful of integers than an identity column, since the values reserved might not be used if the insert fails or is abandoned, but it does allow multiple-row inserts with known id values.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Firstly thanks to you all for helping me out.
I tried what stax68 & GilaMonster mentioned regarding the DBCC but the 2 numbers i get is same (test machine with same DB backup). Also i checked all the code and scripts which works with the table and non inserts identity key as all use this same stored procedure. Still test is underway as this occures suddenly so kept it under test to generate the same senario as it would be on the deployed site.
If possible i would like to know is there any way to find if this identity key ever changed or was our of sync. As i want to find a work around soon because generation of the same error would take some time on the test envoirnment.
Kindest Regards,Rakesh Salian
-
I'd set up a Profiler trace, filtering for the Transaction table, to record the sequence of events that lead up to the error happening. This should give you some clues as to what's going on.
-
could you script out the PRIMARY KEY constraint and post the DDL? I have an idea... and you definitely ran DBCC with NORESEED first, before RESEED?
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply