Dynamic Process Properties passed from database cause an error, but static values do not

Question

We have a number of Microsoft SQL servers we access via linked servers. Before we had a lookup table in Boomi to record this information as it is stored against a location, however this is constantly in flux so it was moved to a SQL database.

When we attempt to connect to a linked server by retrieving the values from the database call we get:

Embedded message: Errors occurred while building or executing SQL statement: An invalid schema or catalog was specified for the provider "MSOLEDBSQL" for linked server "**removed**".; Caused by: An invalid schema or catalog was specified for the provider "MSOLEDBSQL" for linked server "**removed**".

However if I set the values that are being passed statically with a set properties shape we do not get this error and retrieve the expected result. I have checked the values of the DPP's both after retrieving them from the database and after setting them manually and they are identical.

Any suggestions?

Answer 1

Turns out this had nothing to do with Boomi, what was happening is it was retrieving the information correctly, however the SQL DB was in ANSI mode meaning the response was padded with spaces, these didn't show when outputing the values but as soon as I converted it to JSON they showed.