INSERT statement conflicted with the CHECK constraint
So a couple things to figure out here:
1. what is the constraint
2. and what field
SQL is telling you the table that it has the issue with – the PA10001 which is the Timesheet Detail information piece.
OK, but what is the field?
Dynamic names the constraints nicely – using the table name and the column then a random value after that.
So if you look at the PA10001 table in SQL, you can see the column
select PATE, * from PA10001
You’ll see that it is a datetime column in SQL
if you run:
SQL will tell us:
ALTER TABLE [dbo].[PA10001]
WITH CHECK ADD CHECK ((Datepart(day, [PATE])=(1) AND Datepart(month, [PATE])=(1)
AND Datepart(year, [PATE])=(1900)))
So looking at the constraint we see that the there is a constraint on the “date” portion of this datetime. And specifically it has to be 1/1/1900.
If we look at the table descriptions in GP for this table, we can find the PATE column – the field is the PA Time End field.
If we look at the “add line item” on the map – in SmartConnect the field is called “End Time”.
So what is happening is that you likely have this mapped and you are passing it a datetime value where the “date” isn’t 1/1/1900 which throws this sql error.
The client’s csv spreadsheet shows the PA Date with various dates – 5/16/2016 would be one of them We don’t even map to the PATE field.
Is anyone available to assist with this?
we’d have to take a look at this then and would need you to open a support incident.
otherwise the functionality of the proc is actually Microsoft and if you aren’t mapping the Time End field (and it isn’t required) then you perhaps should work with their support team.
provide them the xml you are using by setting the output to Dynamics GP – File and they can take a look at it to determine your issue.
If you would like to submit an answer or comment, please sign in to the eOne portal.