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!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Workflow completes without error but does not complete SQL server stored procedure

JHerzog
7 - Meteor

I'm running a workflow that calls a SQL server wrapper stored procedure that calls a bunch of other SQL server stored procedures in an Output tool as a post sql statement. If I run this wrapper procedure in my SQL Server environment directly it runs to completion in about 1 hour and 25 minutes. When I run it using Alteryx it stops on the same step each time about 45-55 minutes into the workflow. No errors are thrown and the status in the Gallery says it was successful.

 

My question is:

1) Is there a Gallery timeout setting for SQL server processes?

2) Is there a timeout setting in the Output tool or the workflow that I need to set? If so I don't see one.

3) Is there a timeout setting in my SQL server that I need to adjust? 

 

Thanks

 

 

4 REPLIES 4
DanC
Moderator
Moderator

Hi @JHerzog,

 

There is no Gallery timeout setting specifically for SQL Server processes. However, there is a timeout setting for workflows in general run on the Gallery. If your workflow exceeded this limit, you would have received a message indicating such. There is a timeout setting on the SQL Server so you might want to check with your DBA on that. Another thing you can try is to add a NOCOUNT command within the stored procedure:

 

From https://docs.microsoft.com/en-us/sql/t-sql/statements/set-nocount-transact-sql?view=sql-server-2017:

 

For stored procedures that contain several statements that do not return much actual data, or for procedures that contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

Please give that a try and let me know if that resolves the issue.

 

Thank you!

 

Dan Chapman
Program Manager, Customer Support
New to the community? Get started here.
DanC
Moderator
Moderator

Hi @JHerzog,

 

Did any of my suggestions help? If so, could you please mark the post as the solution? If not, please let me know if I can help further. Thank you!

Dan Chapman
Program Manager, Customer Support
New to the community? Get started here.
JHerzog
7 - Meteor

I contacted our Alteryx Gallery admin and they stated there was a 3 hour timeout. However, my process was ending around the 2 hour mark. So a timeout was not the issue.

 

I added the NOCOUNT setting to all my stored procedures and I still hit what appeared to be a timeout.

 

I then went through all my stored procedures to check for any SELECT statements that might be causing the Alteryx process to exit prematurely because it has received output. I did find 2 such statements. So I removed those. After that I hit an error stating the SQL Server user I had logged in with did not have rights to read from a specific schema. Once I fixed all those issues the SQL stored procedure wrapped and all its sub procedures completed successfully.

 

Lesson learned, do not have output from a SELECT statement in any of your stored procedures.

The other login issue was a separate problem which finally arose after removing the SELECT returns from the stored procedures.

DanC
Moderator
Moderator

Thank you, @JHerzog, for sharing your findings. We really appreciate the details. This is sure to help others in the future!

 

Thanks again!

Dan Chapman
Program Manager, Customer Support
New to the community? Get started here.
Labels