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

[JHerzog]

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

[DanC]

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!

[DanC]

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!

[JHerzog · Accepted solution]

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]

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

Thanks again!