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

s_pichaipillai

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