Troubleshooting Stored Procedures

[AlexKo]

You’ve connected up to your database and started pulling data into Alteryx, but suddenly something terrible happens: that Stored Procedure you need just won’t work no matter what configuration you try.

But what’s that!? Is it a bird? Is it a plane? No, it’s Alteryx Customer Support with a handy checklist of troubleshooting tips!

1. Are you using Microsoft SQL Server, Oracle, or SAP Hana?

Currently (as of version 2018.3 that is), we only support executing stored procedures using these connection types.

For Microsoft SQL Server, make sure you’ve gone ahead and updated to the latest SQL Server Native client driver. To check out which driver you’re using for your connection, check the ODBC Data Source Administrator:

Click “Add” and you will see a list of Drivers to connect to. Select the SQL Native Client.

2. Is your Stored Procedure returning data?

Alteryx loves data, and when data stands up Alteryx at the movie theatre holding that extra-large buttered popcorn, Alteryx gets really sad and doesn’t even feel like watching the movie anymore.

Awkward analogies aside, the Input Tool does expect to pass records to the rest of the workflow, and if it can’t it will return an error.

What this means is that if you have a stored procedure doing some work in the database but not actually returning data, you’ll need to trick Alteryx by including a SELECT statement at the end.. Something like:

SELECT COUNT(*) FROM [table]

3. Still not working?

Sometimes that stored procedure is doing a lot of complex things and Alteryx gets a little confused trying to keep up. It’s trying, but you’re a SQL wizard and you can’t help being this gosh-darn talented all the time. In this case, we may have to hide away the mechanics of the sproc and tell Alteryx to “pay no attention to that man behind the curtain”.

 Not magic! Just self-service analytics!

Let’s put that stored procedure inside of another, that way, to Alteryx, it looks like we’re just executing one statement. The original stored procedure will do the heavy lifting, and the wrapper sproc will just pass it on to Alteryx with a nice little bow on it.

Here’s an example that’s worked for clients in the past:

ALTER PROCEDURE [dbo].[wrapper_sp]

AS

BEGIN

SET NOCOUNT ON;

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

if 1=2

begin

       select

CAST(NULL AS nvarchar(255)) AS 'Field1_name'

,CAST(NULL AS nvarchar(255)) AS 'Field2_name'

,CAST(NULL AS nvarchar(255)) AS 'Field3_name'

,CAST(NULL AS nvarchar(255)) AS 'Field4_name'

,CAST(NULL AS DATETIME) AS 'Field5_name'

end

EXEC DBO.original_sp

END

One thing to note is we’re aliasing our fields from the original stored procedure explicitly to the wrapper. It’s all in an “if” statement that prevents that bit from ever actually being executed. Also, make sure you are invoking “SET NOCOUNT ON”.

4. Working with temp tables?

Error: Input Data (1): Error: No target OLEDB object available. in Query: Exec stored_procedure_example ‘table'

Temp tables are a tough one, and for this you may need an OleDB connection, your DBA, and Leonardo DiCaprio.

We need to go deeper.

Basically, there are only so many layers Alteryx can see through, and temp tables are not in those layers. To get around that we need to do 2 things:

1. Use a GLOBAL TEMP table so it’s visible across all sessions
2. Place it in the aforementioned wrapper sproc with “SET NOCOUNT ON”

CREATE PROCEDURE [dbo].[original_sp]

AS 

 if Object_ID('tempDB..##tmpresults') is Not Null

  begin

       drop table ##tmpresults

  end

 Select 1 as ID into ##tmpresults

Select * From ##tmpresults

GO

=====================================

CREATE PROCEDURE [dbo].[wrapper_sp]

AS 

                SET NOCOUNT ON

                Exec original_sp GO

GO        

Disclaimer: Alteryx Customer Support does not support writing or editing SQL code. These tips have been compiled from past cases in which we’ve worked with clients to help troubleshoot connection issues. The examples in this article have been altered to remove any features that may reveal sensitive information.

Special thanks to @MargaritaW, @ARich, and @LindaT for lending their time and expertise to this article.