integrityerror in Executing SQL Statement exec

creativegenius786
creativegenius786 used Ask the Experts™
on
I am getting the following error from logs of my application

00:02:21 : [SQLDatabases] integrityerror in Executing SQL Statement exec sp_insertHour '<t><xdat><resid>17179</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17181</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17180</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17177</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17178</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17039</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17158</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17159</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17182</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17160</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17157</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat><xdat><resid>17183</resid><statid>152</statid><timeStamp>1315848600</timeStamp><avg>86400.000000</avg><min>86400.000000</min><max>86400.000000</max><count>1</count></xdat></t>', 'tblHour2011255' - [Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK__tblHour2011255__33380113'. Cannot insert duplicate key in object 'dbo.tblHour2011255'. in EXEC

how to fix this error
Error-log.txt
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
You should delete the duplicate or change the table constraints for 'PK__tblHour2011255__33380113'

Author

Commented:
How to find the duplicates?
Use the attached code against the database in question and identify the column name for the table where the constraint is applied to. Then compare the values from the column to the ones you are trying to insert.

If they exist in the column and the insert then it is duplicated. You should either remove the row from the insert or the table.

A good practice would be to apply logic that validates the uniqueness of the data being inserted. This will prevent future errors.

If the data insert is legal and duplicate values are required then you should rethink the table logic.
 

select 
sysobjects.[name] as [contraint]
,[tbl].[name] as [table]
,[col].[name] as [column]
from sysobjects 
join (select [name]
				  ,[id] 
			from sysobjects 
			where [xtype] = 'u') as [tbl] on [tbl].[id] = sysobjects.[parent_obj] 
join sysconstraints on sysconstraints.[constid] = sysobjects.[id] 
join syscolumns [col] on [col].[colid] = sysconstraints.[colid] 
					 and [col].[id] = [tbl].[id]
order by [tbl].[name]

Open in new window

Author

Commented:
ok

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial