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!

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
AlexKo
Alteryx Alumni (Retired)

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:

 

1.png

 

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

 

2.png

 

 

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.

 

3.png

 

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

 

1.jpg

 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.

 

4.png

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.

Alex Koszycki
Program Manager, Community Platform

Alex is acutely aware of all the sleep he's lost wrangling gigantic data-sets. But that's ok; now he gets to work with the Alteryx Community, spreading a new culture of analytics. Get it done quicker, automate that task, and have more time to think about the bigger picture. Also it's fun, so there's that.

Alex is acutely aware of all the sleep he's lost wrangling gigantic data-sets. But that's ok; now he gets to work with the Alteryx Community, spreading a new culture of analytics. Get it done quicker, automate that task, and have more time to think about the bigger picture. Also it's fun, so there's that.

Comments
awilkey
5 - Atom

I recently ran across this issue, recieving the (Error: Input Data (1): Error: No target OLEDB object available.) error when attempting to use a stored procdure with a temp table as a data input.

 

It puzzled me, because I knew that I had successfully done this in the past.

 

What I eventually discovered was that, in the procedure that was failing, the temp table was being created implicitly through the use of an sql statement like 'Select into #temptable from blah blah'...but when I looked at the procedure that did NOT throw any error, I saw that the temp table in that procedure was created explicitly using sql like 'Create table #temptable (field1 varchar(10), etc.) then populated using an 'insert into #temptable select * from blah'. 

 

So I changed the implicit sql in the first sproc, created the temp table explicitly, and following that change, was able to use the stored procedure as a data input successfully.

AlexKo
Alteryx Alumni (Retired)

Thank you for adding that @awilkey! It's a great tip to note and I can guarantee it will save someone a lot of trouble if they encounter it.

ralph2048
7 - Meteor

Being quite far from being a SQL expert, I am trying to understand 

3. Still not working?

 I is this placed into the Choose Table or Specify query SQL Editor?

 

Do you have an actual working example of this for me to tweak?

 

 

SeanAdams
17 - Castor
17 - Castor

Excellent post @AlexKo - I've run into many of these (in a previous life as a software developer mainly) - and the NoCount is a switch that is super useful and not well known.

 

Thank you for this!

NPT
8 - Asteroid

I was stuck with the error "No target OLEDB object available".  The answer to #4 worked perfectly, thanks!

 

~ Nathan

AndrewBuckley
5 - Atom
I found that it was necessary to put an arbitrary select statement at the start of my stored procedure. The below worked. alter procedure dbo.sp_MaterialiseView @parameters as begin set nocount on; select 1 as [Output]; prepare dynamic sql; execute dynamic sql; end Having the select 1 as [Output] after the dynamic sql did not work. Using ODBC Driver 13 for SQL Server, on Version 2019.1.4.57073 of Alteryx and connecting to an instance of SQL Server 2016...
HWhiz
5 - Atom

An application such as Alteryx typically needs to know the datatypes that will be returned by a Stored Procedure before they are returned. However if the proc is to complicated in certain ways SQL server cannot work out the metadata for the proc's result set. In TSQL you can check the result set metadata for a particular proc using:

 

EXEC sys.sp_describe_first_result_set

 

If you get an error calling this to identify the metadata, that means SQL server cannot work out the datatypes. This in turn means it cannot tell Alteryx what they are. The simple work around in TSQL is to define the metatdata for the result set up front using the WITH RESULT SETS option. Such as:

EXEC sys.sp_tables
WITH RESULT SETS
(
    (
        TABLE_QUALIFIER sysname
      , TABLE_OWNER sysname
      , table_name sysname
      , TABLE_TYPE VARCHAR(32)
      , REMARKS VARCHAR(254)
    )
);

 Looks like Alteryx needs row counts disabled in the proc or a pre-SQL statement. 

Ajith92
5 - Atom

Hello All, I am calling a SQL Server stored procedure from Alteryx workflow, but it's not executing after some point.

I have added the statements below in the proc, but that didn't help.

SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

 

 

Also, I have removed unwanted select statements from the script and formatted the Tsql. And tried calling proc from another proc. But nothing helped me.

The total number of lines in the PROC - is 8999, Execution working fine till line number 7766.

The script contains temporary tables as well.

 

Can anyone suggest some solution to this issue?

 

Thanks in advance

Aj