Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Stored Procedure Error "No Columns Returned"

MarshallG
8 - Asteroid

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

 

12 REPLIES 12
dataMack
12 - Quasar

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
8 - Asteroid

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).

 

 

Capture.PNG

dataMack
12 - Quasar

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
8 - Asteroid

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
Alteryx
Alteryx

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
12 - Quasar

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

ODBC.PNG

2. OLEDB

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

OLEDB.PNG

 

finally both way returns data

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

hope this helps

MarshallG
8 - Asteroid

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
7 - Meteor

Hi Bolide,

 

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

jk_dup_463
5 - Atom

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?

Labels