Insert or update sometimes throwing primary key error

Question

Post reply

Insert or update sometimes throwing primary key error

Mike M-384057

SSC Enthusiast

Points: 106
More actions
September 19, 2008 at 5:20 am

#212894

Good Morning everyone,
I've run into a weird scenario and managed to stump my friend who is usually my go-to sql guru. I've got a stored procedure that I use to log page hits on several websites. I have a table that stores the number of times a user with a given IP address hits a site each day.
Table: tblSiteUniqueUsers
PK site_ID uniqueidentifier
PK ipaddress varchar(20)
hits int
PK dayOfHits datetime
I always pass in dates with no time to the dayofhits, so just the date and all zeros, that way it will group together.
My stored procedure does the following: (just posting the important part)
IF (SELECT count(Site_ID) FROM tblSiteUniqueUsers
WHERE Site_ID=@Site_ID AND ipAddress=@ipAddress and dayOfHits = @currDate) >0
BEGIN
UPDATE tblSiteUniqueUsers
SET hits=hits+1
WHERE Site_ID=@Site_ID AND ipAddress=@ipAddress and dayOfHits = @currDate
END
ELSE
INSERT INTO tblSiteUniqueUsers (Site_ID,ipAddress,dayOfHits,hits) VALUES (@Site_ID,@ipAddress,@currDate,1)
END
To start with, it works. The issue I've got though is that every so often (maybe 3 or 4 times a day over 1000 hits) I get a primary key violation. How could this happen? Any ideas?
Many thanks in advance!
Mike

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 1

create table tbl12 (

date datetime primary key)

insert into tbl12

select '2008-09-19'

union

select '2008-09-19'

see the script aboce..it has same story as ur Stored proc

actaully as u said u are inserting same date more than once (as i m doing above)

its creating primary key violance

do one thing ..insert complate datetime instead of date only

and extract later whenever u actually need it ...or ..remove primary key bcoz it is use less

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Chandra Sekhara Vyas Dhara SSChampion Points: 10207 More actions · Answer 2

You need to pass PK dayOfHits datetime with time aslo. This will resolve your problem.

Mike M-384057 SSC Enthusiast Points: 106 More actions · Answer 3

I see, makes sense. I suppose what I'll do is remove the date field from the PK fieldset, then just pass in the exact datetime and when i run my report, i can group the results by the day in my query to get the same result. It's still odd to me that it would happen this way because i'm passing in a datetime.Date from asp.net, which sends in the date plus :00:00:00 for the time. When i do the select count, you'd think that it would pick up on it, but for some reason the datetime type is funny with the 0's. In any case, I'll change my strategy since it does indeed look like datetime weirdness causing the problem.

thanks a million guys!