When running a Stored Procedure from the Input or Dynamic Input tool the following error (or similar) will occur.
Error opening "EXEC StoredProcedure": "No Columns Returned"
- Alteryx Designer
- SQL Server
Both Input tool as well as the Dynamic Input tool require in the main SQL section a statement that returns columns.
ODBC connection throws this error, but OLEDB does not.
Defect: TDCB-3694 - SQL Server ODBC errors No Columns Returned with Stored Procedure.
Resolution for Cause #1:
Amend the Stored Procedure to return columns by modifying the SQL query of the Stored Procedure on the database side. If you are unsure how to do that you can work on it together with your DBA.
Resolution for Cause #2:
Defect TDCB-3694 is target for resolution in future release.
Connect using an OLEDB connection instead of an ODBC connection and re-test the Stored Procedure as is.
If you add the metadata at the end of the stored procedure, it runs successfully without any error. You can define the result set when calling the stored procedure, like this:
Alternatively, the Stored Procedure can be called within the Pre- or Post-SQL query section of the Input tool.