Key Violation

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Thanks for the fast response! How do I go about correcting this?

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.

ASKER

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

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

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

thanks!

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?

ASKER

will be working on it soon. will be in touch. thank you.

ASKER

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.

ASKER

i have increased the point value

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.

ASKER

here are the columns according to "sp_help fastrack":

USER_NAME
HD_INFO
HD_LABEL

This will identify the primary key columns:

SELECT Column_Name
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE Table_Name = 'Test'

ASKER

OK, that query returned the following results:

Column_name

1 HD_INFO
2 USER_NAME

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.

ASKER

ran query, no records back

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.

ASKER

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

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.

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'

ASKER

ran the query, no records back.....any ideas?

unfortunately im out of them. I think this is definately one to beat up the vendor on.

ASKER

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

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.

" So I deleted the row myself. " sounds to me like a definate bug in the software if it is trying to insert duplicates.

ASKER

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