Error when outputting to SQL Stored Procedure

Question

I'm currently working on updating a stored procedure based on workflow results, but I'm encountering an error in the Output tool despite the stored procedure updating successfully.

Error Message:

Error: Output Data (127): Error opening query: Microsoft OLE DB Provider for SQL Server: Invalid column name 'SubIndustryType'. [42S22 = 207] Microsoft OLE DB Provider for SQL Server: Invalid column name 'IndustryType'. [42S22 = 207] Microsoft OLE DB Provider for SQL Server: Invalid column name 'ClientType'. [42S22 = 207] ... (additional invalid column names: 'ExternalReferralType', 'OriginationDetail', 'OriginationRollUp', 'BDSignedOffName', 'FuzzyMatchName', 'CurrentAgressoName', 'ProjectCode', 'Client ID')Context:

* The stored procedure interacts with three tables: core.ACL, core.ACL_Code, and core.ACL_Projects.

What I've Done:

* Included screenshots (attached) for additional clarity.

* Verified that the stored procedure updates despite the error.

Any guidance or suggestions would be greatly appreciated!

Screenshots blacked out for confidentiality.

VinodKammula28 · Answer

Hi @spunia

Following up on the stored procedure issue in the Alteryx workflow, here are three validated approaches to resolve the "Invalid column name" error generated by the Output Data tool:

Option 1: Disable "Show Transaction Messages"
In the Output Data tool configuration, uncheck the box labeled "Show Transaction Messages."
This stops Alteryx from trying to validate return columns from the stored procedure, which prevents the error.

Option 2: Move EXEC to "Pre Create SQL Statement"
Cut the EXEC command from the "Post Create SQL Statement" field and paste it into the "Pre Create SQL Statement" field.
This ensures the stored procedure runs before Alteryx checks for schema validation, eliminating column name issues.

Option 3: Add a Dummy SELECT After the EXEC (Advanced)
If the EXEC must stay in the "Post Create SQL Statement" field, wrap it like this:

BEGIN
EXEC acl.UpdateDataFromFlow
@ClientId = 80000,
@ProjectCode = 81000,
...,
@SubIndustryType = 'PLTD';
SELECT 1 AS DummyResult;
END

This provides a placeholder result that satisfies Alteryx's parser, preventing it from misinterpreting parameter names as column names.