Stored Procedure Error "No Columns Returned"

[MarshallG]

We connect to a remotely-hosted SQL Server 2008 DB on which we can execute Stored Procedures, however we have no ability to edit/write Stored Procedures (or even see the code through which the exisiting SPROCs were created). 

In SQL Server Management Tools, we are able to return results using the code below. However, no matter what I try, I am unable to return results in Alteryx -- and unfortunately, no one at our organization is a SQL expert (including myself, needless to say :-) ).  

Any leads, suggestions, workarounds appreciated!

thanks,

marshall

USE [Database1]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[spRPT_Annual_Report]
@Reporting_Period = N'Use Custom Period',
@StartDate = N'12/01/2014 00:00:01',
@EndDate = N'11/30/2015 23:59:00',
@Universal_or_Grant_Report = N'1 - Universal'
SELECT 'Return Value' = @return_value
GO

[dataMack]

You can't execute the code you have directly since its specific to MS SQL Server mgmt.  In Alteryx, you would click on the 'Stored Procedure' tab within an Input Tool that you have connected to that SQL server database and just setup the values to be passed into the procedure.  You could even use a Dynamic Input to pass those values if they change based on other data upstream in your Alteryx workflow.

[MarshallG]

My apologies. I should have specified that I have attempted to use the Stored Procedure section of the Query Builder without success. One point that may provide a clue is that when I click to the Stored Procedures tab, the stored procedures are listed, however the parameters associated with it do not populate (see screenshot below).

[dataMack]

Have you tried the Dynamic Input tool?  if you follow these instructions:

http://help.alteryx.com/9.5/DynamicInput.htm

I'd be curious to know if the parameter drop downs don't popluate there either?

[MarshallG]

Thanks for your help, Jason!

Per your question, even when using the dynamic input tool, I still can't see the parameters in the dropdown.

[LindaT]

Are you using the SQL Server Native Client for your connection?  The Stored Procedure parameters won't show up with the standard SQL Server drivers.  You can download it from Microsoft here: https://msdn.microsoft.com/en-us/sqlserver/ff658533

[s_pichaipillai · Accepted solution]

i was trying re produce the issue with my SQL Server 

and i see the Diffrence between ODBC and OLEDB Dirvers as below

ODBC:

1. When you navigate to the SProcs you wont see the Parametrs but the work around is go to the SQL Editor and Add your Paramater

See the ScreenShot below

2. OLEDB

Create OLEDB connection and Navigate to the SProcs there you can see your SP with Parametrs. see the below 

finally both way returns data

Note: Make sure you SP returns Column Names when you Execute it SSMS

hope this helps

[MarshallG]

Thank you @s_pichaipillai. I'm not entirely sure why but OLEDB is returning results, while the ODBC is not. All I needed to do was switch the connection driver.

Using the ODBC connector, I had tried many different combinations of putting the parameters in the SQL Editor as suggested, tried declaring them in the Pre-SQL state, tried executing the StoredProc using the InDB connector, etc without success.

-Marshall

[html82]

Hi Bolide,

For ODBC option, how you manually add parameter, I tried but it didn't work.

[jk_dup_463]

Hello,

I'm receiving the same "no columns returned" error message when trying to run a SQL stored procedure through an ODBC connection. Like the original post, the parameter/datatype/value info is blank. I'm using the SQL Server Native Client as the driver and it has not fixed the issue. Any other suggestions?