Sql, Data Table issue: "Execute query : Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints."

datatable
studio
error
sql

#1

Scenario:

When executing a query I’m getting this error when I try to get a column containing a string:
**Execute query : Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.**

Being completely new to UiPath, I did not yet find out how to disable these constraints.

Steps to reproduce:

My flow is as follows:

  1. Connect -> 2)execute query -> 3)For each row (where I use write line row(0).ToString)

  2. My connection is an ODBC source

  3. For testing my query is as simple as: "SELECT productname FROM products"
    This works fine on some columns but many (string) columns result in the error.

I tried adjusting the flow to first set a type for a column name:
Connect -> Build Data Table -> Execute query -> For each row
In ‘Build Data Table’ I added 1 column of string type with column name “productname” (the one I use in the query). This did not solve the error.

Studio/Robot/Orchestrator Version:

UiPath Studio Community Version 2016.2.6344 Stable

OS Version: Windows Server 2012 R2 Standard 64-bit. .NET 4.0.30319.42000

Hopefully someone can point me in the right direction, or can make some suggestions what to check


#2

This is not a UiPath problem it is a constraint on the database. Either you have loaded duplicate values or you have not populated a required field. You need to go directly to the database and check the settings on the table you are trying to update.

Can you run this query successfully from a SQL editor such as Management Studio?


#3

What I’m sure of so far:
Just tried Management Studio, I’m not familiar with it but the following worked fine:
SELECT * FROM OPENQUERY([FILEMAKER SERVER], ‘SELECT productname FROM products’)

note: For Management Studio I only have 64bit to try, it uses an ODBC connection with a 64-bit driver. UiPath sees only the DSN which uses the 32-bit ODBC driver so things might not be equal.

I can do all (select, insert etc) as well using ‘Talend Open Studio for Database Integration’ on the same database. For Talend I use jdbc to connect to the same database. Never had any errors regarding constraints.Considering it’s using jdbc things aren’t equal, but again success, just not with UiPath.

It’s a Filemaker database. There are no restrictions on those fields as far as I know and can see, tried several fields which have no restrictions.

Not my expertise but could it be that UiPath assumes constraints if no schema data is available? While other programs can cope with this scenario?

Any ideas?

Thanks