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

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Post SQL Errors not being Passed Through to Alteryx

I frequently load data to staging tables in SQL and then run a stored procedure via the Post SQL setting in the output tool to merge it in with the destination tables.  I'm finding that if the stored procedure fails due to some error, Alteryx often doesn't notify me.  Instead it simply acts like it completes fine.

 

To reproduce this, I created a simple stored procedure:

 

CREATE PROCEDURE STG.TEST
AS
BEGIN

SELECT 1 AS TEST INTO #TEMP
SELECT 1/0 AS Calc INTO #TEMP2

END

 

When I run this via SSMS, I get this error as expected:

 


(1 row affected)
Msg 8134, Level 16, State 1, Procedure STG.TEST, Line 12 [Batch Start Line 15]
Divide by zero error encountered.
The statement has been terminated.

 

When I run this via a post SQL statement in the Alteryx output tool, the workflow runs fine without displaying the error:

 

Capture.JPG

 

This is a serious issue because without these errors getting passed through, a user has no warning that the data was not successfully loaded.

 

Has anyone else encountered this?  Interestingly if I set NO COUNT ON then the error does get passed through.  This implies that if any records are successfully written to a table (in this case a temp table) then Alteryx will ignore any subsequent errors that are generated.

Alteryx
Alteryx

Hi Daniel,

 

Yes, Alteryx will ignore all subsequent errors unless NOCOUNT is on. 

 

With NOCOUNT off and you run a query, as soon as a record is written or something is done Alteryx will stop even if the stored procedure is supposed to do more stuff. 

 

You can test this by putting

 

SELECT 1/0 AS Calc INTO #TEMP2

in the beginning of the query. You will get the error with NOCOUNT off. 

 

 

Labels