Serverless task in Snowflake

Question

I am executing below statement for serverless task it's throwing an error

CREATE OR REPLACE TABLE TASK_TABLE_2(TBL_NAME VARCHAR, LAST_INSERTED_DATE TIMESTAMP);

CREATE OR REPLACE TABLE TASK_TABLE_3(TBL_NAME VARCHAR, LAST_INSERTED_DATE TIMESTAMP);

create or replace task DEMO_TASK_2
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE ='1 minutes'
as
EXECUTE IMMEDIATE
$$
BEGIN
insert into TASK_TABLE_2 values (2_1,SYSDATE());
insert into TASK_TABLE_2 values (2_2,SYSDATE());
insert into TASK_TABLE_3 values (3_1,SYSDATE());
END
$$
;

Error Message: Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 0 : No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

But the same executing successfully if i use the warehouse in the TASK

CREATE OR REPLACE TABLE TASK_TABLE(TBL_NAME VARCHAR, LAST_INSERTED_DATE TIMESTAMP);
CREATE OR REPLACE TABLE TASK_TABLE_1(TBL_NAME VARCHAR, LAST_INSERTED_DATE TIMESTAMP) ;

create or replace task DEMO_TASK
WAREHOUSE = COMPUTE_WH
SCHEDULE ='1 minutes'
as
EXECUTE IMMEDIATE
$$
BEGIN
insert into TASK_TABLE values (0_1,SYSDATE());
insert into TASK_TABLE values (0_2,SYSDATE());
insert into TASK_TABLE_1 values (1_1,SYSDATE());
END
$$
;

Answer 1

The case is reproducible and my hypothesis is a serverless task does play well with standalone Snowflake Scripting BEGIN END block.

Initial setup(tested using Snowsight UI):

CREATE OR REPLACE TABLE TASK_TABLE_2(TBL_NAME VARCHAR, LAST_INSERTED_DATE TIMESTAMP);
CREATE OR REPLACE TABLE TASK_TABLE_3(TBL_NAME VARCHAR, LAST_INSERTED_DATE TIMESTAMP);

SELECT CURRENT_VERSION();
-- 7.5.2

Task test:

EXECUTE TASK DEMO_TASK_2 ;

SELECT *
FROM TABLE(information_schema.task_history())
ORDER BY scheduled_time DESC;

---

Use case 1:

CREATE OR REPLACE TASK DEMO_TASK_2
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE ='1 minutes'
AS
EXECUTE IMMEDIATE
$$
BEGIN
insert into TASK_TABLE_2 values ('2_1',SYSDATE());
insert into TASK_TABLE_2 values ('2_2',SYSDATE());
insert into TASK_TABLE_3 values ('3_1',SYSDATE());
END
$$;

-- Task DEMO_TASK_2 successfully created.

Output:

Uncaught exception of type 'STATEMENT_ERROR' on line 2 at position 0 : No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

---

Use case 2

No EXECUTE IMMEDIATE, only Snowflake Scripting block

CREATE OR REPLACE TASK DEMO_TASK_2
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE ='1 minutes'
AS
BEGIN
insert into TASK_TABLE_2 values ('2_1',SYSDATE());
insert into TASK_TABLE_2 values ('2_2',SYSDATE());
insert into TASK_TABLE_3 values ('3_1',SYSDATE());
END;

-- Task DEMO_TASK_2 successfully created.

Output:

Uncaught exception of type 'STATEMENT_ERROR' on line 2 at position 0 : No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

---

Use case 3:

EXECUTE IMMEDIATE with BEGIN END block including only single statement:

CREATE OR REPLACE TASK DEMO_TASK_2
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE ='1 minutes'
AS
EXECUTE IMMEDIATE 
$$
BEGIN
insert into TASK_TABLE_2 values ('2_1',SYSDATE());
END;
$$;

-- Task DEMO_TASK_2 successfully created.

Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 0 : No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

---

Use case 4:

EXECUTE IMMEDIATE no BEGIN END and single statement:

CREATE OR REPLACE TASK DEMO_TASK_2
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE ='1 minutes'
AS
EXECUTE IMMEDIATE 
$$
insert into TASK_TABLE_2 values ('2_1',SYSDATE());
$$;

-- Task DEMO_TASK_2 successfully created.

Output:

SUCCEEDED

---

Use case 5:

BEGIN END block with single statement:

CREATE OR REPLACE TASK DEMO_TASK_2
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE ='1 minutes'
AS
BEGIN
insert into TASK_TABLE_2 values ('2_1',SYSDATE());
END;

-- Task DEMO_TASK_2 successfully created.

Output:

Uncaught exception of type 'STATEMENT_ERROR' on line 2 at position 0 : No active warehouse selected in the current session. Select an active warehouse with the 'use warehouse' command.

---

Use case 6:

Single statement:

CREATE OR REPLACE TASK DEMO_TASK_2
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE ='1 minutes'
AS
insert into TASK_TABLE_2 values ('2_1',SYSDATE());

-- Task DEMO_TASK_2 successfully created.

Output:

SUCCEEDED

---

Outcome:

• Task creation - succeeded in all 6 cases
• Task run - succeeded only in case 4,6 (no BEGIN END block)

It should be reported to Snowflake Support.

Answer 2

This worked for me, looks like you forgot to add USE Warehouse for creation of task, as creation of task is not metadata based action , so please use "USE WAREHOUSE " sql statement before you execute create task

create or replace task DEMO_TASK_2
USER_TASK_MANAGED_INITIAL_WAREHOUSE_SIZE = 'XSMALL'
SCHEDULE ='1 minutes'
as
EXECUTE IMMEDIATE
$$
BEGIN
insert into TASK_TABLE_2 values (2_1,SYSDATE());
insert into TASK_TABLE_2 values (2_2,SYSDATE());
insert into TASK_TABLE_3 values (3_1,SYSDATE());
END
$$

;