I have created an integration, and it works fine, but I need to update a SQL Table and an integer status value, so I added an ‘After Document Success‘ SQL task.
update _CompanyID.dbo.SY00500 set Workflow_Status = _Status where BACHNUMB = '_BachID'
The _CompanyID field sets the correct database context and is a string. _BatchID is a string and properly uses single quotes. The Workflow_Status field is an integer, so _Status isn’t using single quotes because it isn’t necessary.
If I take the SQL command into SSMS and replace the variables with the proper values, the SQL runs fine and properly updates the table with the correct value. However, running it in SmartConnect fails with the error:
Task UPDATE_SQL_TABLE failed. Integration run will end reporting failure. Invalid column name ‘Workflow9’
This is puzzling because the SQL Script works perfectly in SSMS.
What is happening here, and how can it be corrected to update my table properly? If you’d like to watch a video explaining this, see below or click here.
What Causes the “Invalid Column Name” Error?
Since the SQL runs correctly from SSMS with the variables hard-coded to the correct values, we know the issue must be caused by the replacement of the variables in the SQL Task by SmartConnect.
Usually, the best way to troubleshoot a SQL Task from SmartConnect is to run a SQL Profile trace so we can see the exact SQL executed by SmartConnnect after the variable replacement. However, in this case, the error itself gives us just enough information to determine why this failed.
The SQL Error, Invalid column name ‘Workflow9’, tells us that SQL cannot find the column named ‘Workflow9’, not that there is a problem with the data being evaluated.
Looking at the original SQL script, the error message is somewhat puzzling since the code is not referencing ‘Workflow’ but instead ‘Workflow_Status‘. Looking closer at the failure on the column from the error, we see it specifies ‘Workflow9‘, but where did ‘9’ come from?
Now this starts to make sense, as it appears that the _Status part of the column name was removed and replaced with a ‘9’, causing ‘Workflow9‘.
Why ‘Workflow9’
As SmartConnect replaced the global and source data in the SQL Task Script, it found a source field named _Status, which is used in two places. It is properly replaced in the “= _Status” part of the SQL Task script, but it mistakenly replaced part of the column name “Workflow_Status” since the name matches there as well.
There are a few solutions to this issue, depending on the nature of the data source for this map. Ideally, we could just change our source column name.
Here, the source is a Text File, so I could easily change the column name using the Create Schema button. If this were a SQL Query source, we could alias the field name. Some of the other data sources allow renaming or removing the columns as well.
If that isn’t possible due to the original source, then we would have to change the SQL in the SQL Task to not let SmartConnect to replace that value.
DOCI SQL Server
If your SQL Server is DOCI and not Binary, we can just change the column name slightly in our SQL task.
Here, I changed the name of the column from the correct name of Workflow_Status to Workflow_STATUS.
update _CompanyID.dbo.SY00500 set Workflow_STATUS = _Status where BACHNUMB = '_BachID'
The ‘replace’ tool being used by SmartConnect is case sensitive so it would not replace _STATUS with the value of our _Status data.
Because SQL Server is installed as non-case-sensitive, it sees the column name Workflow_Status and Workflow_STATUS as the same and doesn’t give an ‘Invalid column Workflow_STATUS’ error.
Binary SQL Server
If your SQL Server is installed as Binary and the previous method won’t work, the last approach is to use Dynamic SQL to hide the column name.
DECLARE @col nvarchar(50) = 'Workflow' + CHAR(95) + 'Status' DECLARE @sql nvarchar(max) SET @sql= 'UPDATE _CompanyID.dbo.SY00500 SET ' + @col + ' = _Status WHERE BACHNUMB = ''_BatchID'' ' EXEC sp_executesql @sql
Build the SQL to execute as Dynamics SQL using the @sql hold, and then break up the “Workflow” and “Status” names using CHAR(95). The Workflow_Status column name isn’t used directly in the SQL Task, so the Workflow_Status field cannot be replaced, but _Status is, which also solves this issue.
If you have any questions about how to fix the “Invalid Column Name” error in SmartConnect, please reach out to support@eonesolutions.com. Our team will be happy to assist.
