Error opening "Exec StoredProcName": No columns returned

[KunalS]

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

[KunalS]

To Add ,

If I try the same steps but connect using OLEDB,

I get the following error message:

Error opening odbc: DRIVER = {SQL Server Native Client 11.0}; UID <user name and password information>: no fields found

Proc runs perfectly fine in SQL server.

[DataPrepChad · Accepted solution]

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 solution]

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"