community
cancel
Showing results for 
Search instead for 
Did you mean: 

Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-to's.
Community v19.6

Looks aren't everything... But the latest Community refresh looks darn good!

Learn More
Alteryx
Alteryx

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@AlexP, 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
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.

Alteryx
Alteryx

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.

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?

 

 

Aurora
Aurora

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!

Asteroid

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

 

~ Nathan

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