October 31, 2013 at 8:49 am
I have a job that call a SSIS package to call a stored procedure that does insertion of records from a table in staging schema (staging.table1) into another table in operation schema (operation.table1). And the job failed complaining that it "... cannot insert duplicate keys in object operation.table1. The duplicate key value is (Oct 28 2013 12:00AM, 2, 5, 15)...".
But if you look at the records below that it supposed to insert they are not really duplicates. They are different in the date column where it has seconds and fractional seconds, but for some reason the SQL Server job cannot see that difference in the date format.
The primary key for operation.table1 is a composite pk of date + id1 + id2 + id3. The staging table has no PK.
The records exists in staging table staging.table1 (to be inserted into operation.table1)
date id1 id2 id3
2013-10-28 00:00:35.5102515
2013-10-28 00:00:35.9172515
Now, if I run the same insert statement for those 2 records from SSMS, it inserts the records fine as it sees the datetime format as is.
I really do not know how to fix this as there is no duplicate in the records. it is the job that cannot see the date for what it really is. Does anyone has run into this issue and have any idea how to fix this, please advise, would be greatly appreciated.
November 1, 2013 at 9:37 am
sqlblue (10/31/2013)
I have a job that call a SSIS package to call a stored procedure that does insertion of records from a table in staging schema (staging.table1) into another table in operation schema (operation.table1). And the job failed complaining that it "... cannot insert duplicate keys in object operation.table1. The duplicate key value is (Oct 28 2013 12:00AM, 2, 5, 15)...".But if you look at the records below that it supposed to insert they are not really duplicates. They are different in the date column where it has seconds and fractional seconds, but for some reason the SQL Server job cannot see that difference in the date format.
The primary key for operation.table1 is a composite pk of date + id1 + id2 + id3. The staging table has no PK.
The records exists in staging table staging.table1 (to be inserted into operation.table1)
date id1 id2 id3
2013-10-28 00:00:35.5102515
2013-10-28 00:00:35.9172515
Now, if I run the same insert statement for those 2 records from SSMS, it inserts the records fine as it sees the datetime format as is.
I really do not know how to fix this as there is no duplicate in the records. it is the job that cannot see the date for what it really is. Does anyone has run into this issue and have any idea how to fix this, please advise, would be greatly appreciated.
My guess is that something about your object definitions or stored procedure is causing implicit conversions of the date that cause you to lose some part of the time information, likely the decimal part of the seconds. If you can post your table definitions and code, we can take a closer look.
Jason Wolfkill
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply