failing on update w/ unique index error. but the index in the error is not unique

Question

Post reply

failing on update w/ unique index error. but the index in the error is not unique

Snargables

SSCrazy Eights

Points: 8855
More actions
July 5, 2015 at 8:06 am

#318828

this index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Snargables SSCrazy Eights Points: 8855 More actions · Answer 1

this is the index

CREATE NONCLUSTERED INDEX [ix_report_history_creative_id] ON [dbo].[DFP_Reports_History]

(

[Dimension CREATIVE_ID] ASC

)

INCLUDE ( [ClientID],

[DateOfdata],

[Dimension LINE_ITEM_ID],

[Impressions],

[Clicks]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

GO

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Are you sure you're checking the correct database? The index mentioned in the error has 5 key columns, the only you posted the definition of only has 1. They're definitely not the same thing.

Also check that there isn't a indexed view with an index of the same name.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Snargables SSCrazy Eights Points: 8855 More actions · Answer 3

exactly. i noticed the same thing. the table has 4 indexes, one of which is unique. the clustered. it's on the below

[ClientID] ASC,

[DateOfdata] ASC,

[Dimension LINE_ITEM_ID] ASC,

[Dimension CREATIVE_ID] ASC,

[Ad_ID_Concatenated_ID] ASC

Snargables SSCrazy Eights Points: 8855 More actions · Answer 4

verified. no indexed view and it's in the right db

EXECUTE sp_MSforeachdb @command1 ='SELECT ''?'',o.name as view_name, i.name as index_name

FROM ?.dbo.sysobjects o

INNER JOIN ?.dbo.sysindexes i

ON o.id = i.id

WHERE o.xtype = ''V'' ';

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

The index that's being complained about has as key columns (BIGINT, INT (or SMALLINT, TINYINT or BIT), DATE INT, INT). Double check the server, database, everything. It's not that uncommon for people to make that mistake. I've made it enough times.

Don't use sysindexes and sysobjects, they were deprecated 10 years ago.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 6

Check the table trigger(s)

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service