How to Update a SQL table in a SmartConnect SQL Task When the Data Contains Single Quotes

If you’d like to watch a video demonstration of how to update a SQL Table in a SmartConnect SQL Task, see below or click here.

In our integration, we need to write back any errors from the integration back to the source data. The error could be from any destination and any error, but this is an example of a GP eConnect error.

For this, we will use a SQL Task to update the source data table with the built-in global variable GlobalLastError.

This works well, however once in a while the SQL task fails with this error.

Task WRITE_ERROR failed. Integration will end reporting failure. Incorrect syntax near ‘1234’

Since my SQL Task appears to be correctly written and almost always writes the error correctly, what causes these SQL Errors and how to I solve this issue?

SQL Task Error

The underlying problem of why you get a SQL failure occasionally is because the error being written is different and contains one or more single quotes.

In Microsoft SQL, typically, string data is represented by using single quotes around the data.

While our SQL Task does have the single quotes used properly, when the error message contains single quotes, this breaks the SQL Syntax due to those unescaped single quotes in the message.

Normally in SQL, we would use the Replace() function to then replace the single quote with two single quotes. However, that doesn’t work here because SmartConnect will enter the entire string into the script and even the “fix” using the Replace() function will fail due to those single quote(s) in the string.

Instead, we need to fix our data outside of the SQL script with something that isn’t affected by the single quote in the error message.

The easiest solution is to use a user defined global variable and a .NET calculated field. Make sure that the .NET task comes before the existing SQL Task since the variable needs to be set first.

The vb.net script for the task is below. The user defined global variable GBL_MSG was already created and not shown.

GBL_MSG = GlobalLastError.Replace(Microsoft.VisualBasic.Chr(39),
  Microsoft.VisualBasic.Chr(39) + Microsoft.VisualBasic.Chr(39))

return true

Here, using the vb.net function – Microsoft.VisualBasic.Chr() was used to make the code more readable as it would be more difficult to distinguish the double quotes and single quotes – but it would have worked as well. The value 39 is the single quote character.

In the .NET task, this will use the Replace function on the global error variable and replace/escape all the single quotes with double single quotes properly.

Lastly, we fix the SQL Task to use our new user defined global variable that is properly escaped if necessary- GBL_MSG – instead of the system variable used previously.

update Pm_trx_data
set ErrString = 'GBL_MSG'
where vendorid = '_vendorid' and docnumber = 'docnumber'

Re-running the map with the new & modified tasks still results in the same destination error message. However, this time the error properly escaped and written to the table with the single quote intact.

Please contact us if you have any questions on how to update a SQL table in a SmartConnect SQL task when the data contains single quotes