Stored Procedures executing fine with individual call statement but failing while calling from either task or another SP

Question

With QUERY_TIMEOUT parameter set to 1 hr, SP execution is getting cancelled automatically if it is running beyond 1 hr. This happens only when it is being called through a task or through another SP. When being called separately in the worksheet or snowsql CLI it is running fine beyond 1 hr and getting completed.

I have a task T1 running a wrapper procedure W1 which calls an actual procedure SP1. Both W1 and SP1 are getting failed with error something like execution cancelled due to runtime crossed timeout limit.

But directly running it in worksheet, call SP1() is not failing at all but getting completed even after 2 hrs of runtime.

Any idea why it is happening? Is the timeout parameter affect all queries, because it doesn't seem so?

Answer 1

Confirm TIMEOUT parameters. Start with Tasks

show parameters like '%TIMEOUT%' in task <task_name>;

The default for Tasks is 3600000 (1 hr). Adjust according to your needs:

ALTER TASK <task_name> SET USER_TASK_TIMEOUT_MS = <new_val>;

Other TIMEOUT params to consider:

show parameters like '%TIMEOUT%' in account;
show parameters like '%TIMEOUT%' in session;
show parameters like '%TIMEOUT%' in user <user_name>;
show parameters like '%TIMEOUT%' in warehouse <warehouse_name>;