SQL Stored Procedure "No Columns Returned"

[nbt1032]

I have a SP with some parameters.  Originally it just did an insert, but I have added a select statement at the end to return something based on good blog here..  The final SP will be a bit more complex than this, but considering I am still struggling with the simple version....

This is the stored procedure which I call from Alteryx using the SQL Native driver.

alter PROCEDURE spCalcCW
    @custSAN int,
    @prodSAN int,
    @tableName varchar(50),
    @SAN int
AS
BEGIN
    SET NOCOUNT ON;
    declare @sSQL varchar(200)
    set @sSQL = 'insert into tlog (desc_, batchid) values (''' + @tableName + ''', ' +  cast(@SAN as varchar(10)) + ')'
    print @sSQL
    exec (@sSQL)

    select top 1 * from tlog order by 1 desc
END
GO

I have table of parameters to process with the SP.  I am able to map everything. 

I have seen several threads on this topic, good tips, but still some problems.   The SP and syntax to execute it is correct when testing in TSQL.  It returns a small 1 row dataset.  I just cannot understand the source of this error.

Thx.

Marc.

[nbt1032 · Accepted solution]

I have spoken with Alteryx support on this.  There were some other posts on this topic that had led me to believe that this would only work with an ODBC connection using SQL Server native driver, which is how my original solution was configured and was throwing the error.  I changed my configiuration to OLEDB and SQL OLE driver and all works now.

Here is what I received back from support.  I have tested and my solution is now working.

So I took a look at your workflow and it was identical to what I was testing with except for one thing. My configuration was using an OLE connection to the database where yours was using and ODBC connection. After changing to an ODBC connection I started getting the exact same errors you were getting. Based on this I checked both OLE and ODBC connections using just a basic input tool, and got the same error with ODBC but not with OLE. As such this seemed to be a limitation with SQL Servers ODBC driver and how it executes stored procedures. So I did a profile trace to see what the drivers were doing when making the SP call. While reviewing the trace I found that they execute the SP in very different ways. The ODBC driver creates a SP called sp_prepare and executes the called sp within in it. This sp_prepare procedure doesn’t seem to produce any usable output which is why you get the error. OLE on the other hand just executes the SP directly.

ODBC SP Execution:

declare @p1 int

set @p1=2

exec sp_prepare @p1 output,NULL,N'EXEC spCalcCW 1234, 4312, ODBC, 1',1

select @p1

OLE SP Execution:

EXEC spCalcCW 1234, 4321, OLE, 1

[EricSpeicher]

I am suffering the same problem.

I am coding my SP and parameters directly into the Input Data tool:

Of course, when I run the workflow, no columns are returned.

If I try to re-open the "Table or Query" I get a response: Unable to find Store Procedure:

But, I can see it in the SQL Editor.

I have tried switching to OLE:

This has no effect on the error.

What else should be tried?

[StevenWhite]

One of the problems with this ODBC workaround is that you can't stream the data out to in-memory tools. It tries to wrap the query in a SELECT * FROM(). 

[vaishalilambe17]

@EricSpeicher  Did you find the solution. I am facing same issue. Please let me know if you find

[EricSpeicher]

Yes, the solution was arrived at with the assistance of Alteryx support [Tyler Nguyen | Customer Support Engineer, Alteryx, Inc.]

1. Instead of choosing your SQL Server as normal, choose "Other Databases"
2. Choose OleDB
3. Choose Microsoft OLE DB Provider for SQL Server
4. Select Next
5. Enter your server name and login information.
6. Select OK

[vaishalilambe17]

Yes I did the same on Friday :-) Thanks.

[ntobon]

If  using ODBC connection the Stored Procedure throws "No Columns Returned" error, but OLEDB does not, that is a known defect. Check this article. The workaround is to add the metadata at the end of the stored procedure defining the result set when calling the stored procedure.