Stored Procedure Error Opening: No Columns Returned

Question

Hi Alteryx Database Connection Team,

I'm trying to connect my stored procs into alteryx. Unfortunately when I run them in AYX I get the attached error.

However, the same exact procedure runs perfectly in SSMS and returns 10 dates & corresponding values. Can anyone offer guidance on how I can solve this issue?

For additional context I'm using ODBC 17.

Thank you!

Zach

AYX ERROR.png

danilang · Accepted Answer

Hi @Zmarx1

Like @mtatum found out in this post, OLEDB is more reliable than ODBC when dealing with stored procedures.  Another thing is that the stored procedure must return a single result set.  SQL Server is often configured to return a record count as well as the data, so add SET NOCOUNT ON to the SP to turn this off.

Dan

Zmarx1 · Accepted Answer

stored proc does return a single set. I've also tried connecting via OLE DB and this still does not work. Any other suggestions you may have?