Solved

Key Violation

Posted on 2004-09-23
27
538 Views
Last Modified: 2010-08-05
Hi all,

Need some more help.  I am in charge of a network consisting of one server running Windows Server 2003, and MSSQL 2000.  The are 12 workstations running Windows XP and SQL client.  All of the workstations share the same databases.  One (1) of the workstations is providing me with the following error:

Key Violation.
Violation of primary key constraint 'pk_fastrack_70A8B9AE'.
Cannot insert duplicate key into object 'fastrack'.

Thanks for you help!

Geoff
0
Comment
Question by:geoffreydlewis
  • 13
  • 8
  • 6
27 Comments
 
LVL 15

Accepted Solution

by:
jdlambert1 earned 250 total points
ID: 12139980
The workstation generating the error is trying to insert a record into the fastrack table with a primary key that already exists in that table.
0
 

Author Comment

by:geoffreydlewis
ID: 12140011
Thanks for the fast response!  How do I go about correcting this?
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12140105
First ask the user what they're doing when they get the error.

Alternatively, or in addition to that, you can use SQL Server's Profiler to trace the SQL Server activity from that workstation.
0
 

Author Comment

by:geoffreydlewis
ID: 12141653
Ok after trace got the following error:

Error:  2627, Severity:  14, State:  1

Basically, it seem to provide the error as soon as the application accesses the database


thanks
0
 
LVL 18

Assisted Solution

by:ShogunWade
ShogunWade earned 250 total points
ID: 12141787
you will need to identify the columns involved in the primary key

try sp_help fasttrack
this will tell you the primary key columns (amongst other things)

then as jd suggests run up profiler and capture the command that is being issued to sql.   Armed with this info you can then check in the fasttrack table for the existing record that has the same primary key value as the user is appempting to insert (or possibly he is issuing an update ans changing the PK of an existing record).  

Is the user using an application or directly manipulationg the database though QA for example.?
0
 

Author Comment

by:geoffreydlewis
ID: 12144972
the user is manipulating the database through an application, not qa.  This client is freshly installed/setup and has never worked properly.

thanks!
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12145000
ok,  I suspect that either the app has a bug in it or the database has somehow been left in an "unclean" state.   did you manage to get the command being issued and the pk columns as described?
0
 

Author Comment

by:geoffreydlewis
ID: 12148011
will be working on it soon.  will be in touch.  thank you.
0
 

Author Comment

by:geoffreydlewis
ID: 12148741
OK, here are some logs generated using profiler.

here is the failed attempt

SELECT USER_NAME, HD_INFO, HD_LABEL FROM dbo.FASTRACK WHERE ((USER_NAME) = ('customer service 4')) AND ((HD_INFO) = ('9002E329'))
Select top 1 HME_Version From HMESU
Select Count(*) from dbo.FASTRACK
BEGIN TRAN
Error: 2627, Severity: 14, State: 1
Insert into dbo.FASTRACK (USER_NAME,HD_INFO,HD_LABEL)
VALUES ('customer service 4','9002E329','´™')

here is a successful attemp performed at the server itself

SELECT USER_NAME, HD_INFO, HD_LABEL FROM dbo.FASTRACK WHERE ((USER_NAME) = ('geoffrey lewis')) AND ((HD_INFO) = ('285D15F7'))
Select top 1 HME_Version From HMESU
Select Count(*) from dbo.FASTRACK
BEGIN TRAN
Insert into dbo.FASTRACK (USER_NAME,HD_INFO,HD_LABEL)
VALUES ('geoffrey lewis','285D15F7','Ào¢')
COMMIT TRAN

remember only one of 12 workstations is having this issue.

here are the columns:
USER_NAME
HD_INFO
HD_LABEL

one thing that caught my eye was the "customer service 4" in the failed attempt.  That is the workstations name...not the login name....

Thank you for all your help.
0
 

Author Comment

by:geoffreydlewis
ID: 12148780
i have increased the point value
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12148876
The profiler log may help us, but as ShogunWade pointed out, we need to know which columns comprise the primary key in the Fastrack table.
0
 

Author Comment

by:geoffreydlewis
ID: 12148934
here are the columns according to "sp_help fastrack":

USER_NAME
HD_INFO
HD_LABEL
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12148967
This will identify the primary key columns:

SELECT Column_Name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE Table_Name = 'Test'
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:geoffreydlewis
ID: 12149203
OK, that query returned the following results:


Column_name

1  HD_INFO
2  USER_NAME
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12149228
Okay, primary keys have to be unique for each row. This means you can only insert 'customer service 4' and '9002E329' into the User_Name and HD_Info columns if they've never been inserted before. The error message indicates that these values already exist in that table.

You can confirm this by running:

SELECT * FROM Fastrack WHERE User_Name = 'customer service 4' AND HD_Info = '9002E329'

If you get a record back from this query, you'll have to troubleshoot the application to determine why it's trying to insert a record where the primary key values already exist.
0
 

Author Comment

by:geoffreydlewis
ID: 12149247
ran query, no records back
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12149272
Then one of two things has to have happened:

1. The application that's generating the error is pointing to a different database (or a database of the same name on a different server) than the one where you ran the last SELECT statement.

or

2. The insert that's failing is part of a transaction where the values are getting inserted, then in the same transaction, the application tries to insert the same primary key values a second time, and when it fails, the entire transaction is rolled back, undoing the first insertion.

Either way, it's clear that this is an application error, not a SQL Server error. Although SQL Server is throwing the error message, that's exactly what it's supposed to do when an application tries to insert data that violates the defined constraints.
0
 

Author Comment

by:geoffreydlewis
ID: 12149290
Thank you very much for all your help.  I will explore this issue on the client side and report any findings or solutions.  I will also contact the application vendor for a possible explanation.  Your assistance has been greatly appreciated.

Geoff
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12149318
Glad to be of some assistance.

BTW, you might point out to the app vendor that if SQL Server were at fault (mis-configured, etc), then it would affect all the workstations.

Good luck.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12158560
one other possibility is that there is an unclosed transaction which is attempting to insert the row.   It might be worth using a dirty read to look for it:


SELECT * FROM Fastrack WITH(NOLOCK) WHERE User_Name = 'customer service 4' AND HD_Info = '9002E329'
0
 

Author Comment

by:geoffreydlewis
ID: 12165559
ran the query, no records back.....any ideas?
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12188252
unfortunately im out of them.    I think this is definately one to beat up the vendor on.
0
 

Author Comment

by:geoffreydlewis
ID: 12209508
Hi,  The vendor connected to the client remotely.  He checked all settings and they seemed to be OK.  He then registered the sql server in ent manager on the client.  Dug down into the  Fastrack table on the problematic database and deleted the row created by the client.  The he was able to log into the fastrack application with no problems.  So I figured "great".  I drove to the office to try it myself.  Sure enough I recieved the same error.  So I deleted the row myself.  I got in once and then the second time I was slammed with the error again.  At this point I am reformatting and reinstalling xp.  Hope this works?!?

geoff
0
 
LVL 15

Expert Comment

by:jdlambert1
ID: 12209526
You should ask the vendor to do it several times, since once obviously isn't enough. I'll keep my fingers crossed for you, but I suspect an XP reinstall won't help.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12224888
" So I deleted the row myself. "   sounds to me like a definate bug in the software if it is trying to insert duplicates.
0
 

Author Comment

by:geoffreydlewis
ID: 12283950
Hi all,

Sorry it took me so long to get back to you.  Reformatting did fix the problem.  Here is why.  This machine had a netbios name 16 characters long.  When the fastrack program filled the row and then tried to remove the row, the removal failed.  So the next logon attempt failed because the row "did" already exist.  Fastrack was reporting the error correctly.  The netbios name of the pc simply cannot be longer than 15 characters and still have fastrack function properly.  I guess I learned a lesson in "keeping it short".  The vendor agreed they should mention that at the initial deployment of the software.  Thanks for all your help, I appreciate it.

Geoff
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12285285
Your welcome
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now