This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
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?
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:
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.
Dan Chapman Program Manager, Customer Support New to the community? Get started here.
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.