Error opening "Exec StoredProcName": No columns returned

Question

I am trying to run a stored proc from alteryx using dynamic input tool.

My proc is defined in Sql server and has no in or out parameters.

When I select the stored proc from a list and go to SQL editor to test it. I get an error message saying

"Error opening "Exec StoredProcName": No columns returned"

I have tried using OLEDB as well as ODBC drivers.

I have gone through various solutions in community without any success. Any help will is appreciated.

Please see the screen shot attached when i try the steps using ODBC driver

AlteryxError.jpg

DataPrepChad · Accepted Answer

Hi @KunalS,

Running stored procedures in Alteryx using either an Input Data or Dynamic Input is awesome, however there is one quirk.  Each of these tools is actually expecting there to be some data returned, so even if the sproc isn't supposed to return anything, you still need to add a dummy SELECT statement at the end of the stored procedure script.

I've used this method in the past with a sproc that deleted specific records, so I had no intention on returning anything.  I ended up dumping the deleted records to a temporary table, then selecting the record IDs from that table at the end of the sproc so Alteryx would actually return something and not give that error.  In the end, it actually worked to my benefit as I could then keep track of records that were automatically being deleted.

Thanks!

~ Chad

dataprepu.com

KunalS · Accepted Answer

Thank you chad. I figured that part out over the weekend.

To add to your solution, every begin end section in the stored proc should select something .

I did a summy "select 1"