When connecting to an external table with user defined Data Types, you'll run into the following error:
A workaround for such situations is to manually add these tables on Integration Studio.
On this article, we'll see how to identify such situations where this workaround is useful. Also, we'll describe how to perform the workaround in a step-by-step guide.
On our particular example, we'll use a SQL Server DB.
Identifying User Defined Data Types
Besides running into the previously shown error on Integration Studio, if you check the platform's Error Log, you may find messages such as the following:
SQLServer compliance error: SQL type (pCustID,-9,-9) found.
If we really want to make sure this is a User Defined Data Type, we may look into the column definition on SQL Server Management Studio:
Manually adding the table on Integration Studio
Even though if we got to this stage we've already performed some of these steps, for completeness sake of this guide, we'll start from the very beginning.
1 - Create a new Database Connection
In order to achieve this, we should enter Service Center → Administration → New Database Connection.
We should then populate the Database Information. In our example, the table we are having issue importing has the name Integration_table_1, and it’s located in the mps_integration_test database. After we successfully test the connection, we’re ready to move on.
2 - Add the table in our Extension, via Integration StudioNow, on by editing our existing extension or creating a new one, we’ll add the problematic table via the “Add Entity” option.
At this point, we’re going to add the Attributes names according to the database columns. As you see, we’ve entered the Table Name Integration_table_1 (the one we’re not able to integrate via the wizard), and we’ve set the Logical Database name as mps_integration_test (as this was the name we defined for the connection on step 1).Because CustId is our PK automatically increased on our DB side, we’ve set the Auto Generated to true. Once we’ve added all the columns and attributed them the correct Data Types. We can go ahead and publish the extension.
3 - Setting the Database connection for the Extension we’ve created
Up next, on Service Center → Factory → Extensions we search for our extension. We’re going to select the Database Connection we’ve previously created.
4 - Adding the extension on our consuming eSpace
On Service Studio, we add the target extension as our dependency.We should now be able to use our table like any other entity.