Execute query gets error when it retrieves default values

Help
1

Scenario:

When ‘execute query’ activity is executed, it returns only a datatable without having default values.
If any default value is included in the output, UiPath Studio shows the error message.

Error Message:
“Execute query: Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.”

I don’t get any error when the same query is executed on SQL editor…

Current Behavior:

When ‘execute query’ returns a datatable with default values, It gets the error.
(the defaults values are ‘0’ for the column ‘cancel’ and ‘0000-00-00’ for the column ‘cancelDate’),

For example

Expected Behavior:

Output a datatable with default values as well.

Current version: UiPath Studio Community version 2018.4.0
OS Version: Windows10 64bits

2

@Shin_Sam - Are there any constraints on these columns which have default values? Could you delete the constraints and try?

3

Thank you for your reply sreenivasm :slight_smile:

I found that Not Null + Default constraints don’t actually write the values on the rows and those columns have ‘Not Null’ and ‘Default’ for the constraints.

I assume that that causes the error.

However, I can’t remove them in my situation…

Do I have any other way to try something?

4

@Shin_Sam - Are these the only 2 columns in this table? Are there any other columns? Would you be able to share the table metadata? The error you see is just a simple SQL error complaining about duplicate data in a column which is supposed to have unique values. If you fix this so that no duplicate values are written to the columns with the unique constraints, then this issue will go away.

The ‘Not Null’ and ‘Default’ will not cause this error. There is another constraint on some column or columns in the table with a primary key or unique constraint which is getting duplicate data.

5

Hi sreenivasm,

I found what causes the error.
The ‘cancelDate’ column is DATE format and the ‘Execute query’ activity doesn’t recognise ‘0000-00-00’.

So when I change the default value of ‘cancelDate’ to a valid date like ‘1971-01-01’, the ‘Execute query’ activity doesn’t make any error.

Thank you for your help so far :slight_smile: