Key Violation

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
geoffreydlewisAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jdlambert1Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
geoffreydlewisAuthor Commented:
Thanks for the fast response!  How do I go about correcting this?
0
jdlambert1Commented:
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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

geoffreydlewisAuthor Commented:
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
ShogunWadeCommented:
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
geoffreydlewisAuthor Commented:
the user is manipulating the database through an application, not qa.  This client is freshly installed/setup and has never worked properly.

thanks!
0
ShogunWadeCommented:
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
geoffreydlewisAuthor Commented:
will be working on it soon.  will be in touch.  thank you.
0
geoffreydlewisAuthor Commented:
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
geoffreydlewisAuthor Commented:
i have increased the point value
0
jdlambert1Commented:
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
geoffreydlewisAuthor Commented:
here are the columns according to "sp_help fastrack":

USER_NAME
HD_INFO
HD_LABEL
0
jdlambert1Commented:
This will identify the primary key columns:

SELECT Column_Name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE Table_Name = 'Test'
0
geoffreydlewisAuthor Commented:
OK, that query returned the following results:


Column_name

1  HD_INFO
2  USER_NAME
0
jdlambert1Commented:
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
geoffreydlewisAuthor Commented:
ran query, no records back
0
jdlambert1Commented:
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
geoffreydlewisAuthor Commented:
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
jdlambert1Commented:
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
ShogunWadeCommented:
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
geoffreydlewisAuthor Commented:
ran the query, no records back.....any ideas?
0
ShogunWadeCommented:
unfortunately im out of them.    I think this is definately one to beat up the vendor on.
0
geoffreydlewisAuthor Commented:
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
jdlambert1Commented:
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
ShogunWadeCommented:
" So I deleted the row myself. "   sounds to me like a definate bug in the software if it is trying to insert duplicates.
0
geoffreydlewisAuthor Commented:
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
ShogunWadeCommented:
Your welcome
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.