I wrote a task that calls the procedure every weekend.

Here is my procedure that inserts the values into a table

CREATE OR REPLACE TABLE TABLE1(DATABASE_ VARCHAR, TABLE_ VARCHAR); // Table to store values

CREATE OR REPLACE PROCEDURE TASK_()   //Procedure to insert values into table
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$
        var str = '';
        var stmt = snowflake.createStatement({sqlText: "INSERT INTO TABLE1 VALUES ('DB1','TB1')"});
        stmt.execute(); 
        return str;
    $$;

Here is my task that calls the above procedure every weekend.

CREATE TASK mytask_hour
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = 'USING CRON 0 0 * 1-12 SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
as
  call TASK_();

But when I checked, the above task didn't run and the values were not inserted into the table.

So, I tried to debug my task and wrote a task that calls the above procedure every minute.

create task mytask_hour
  warehouse = COMPUTE_WH
  schedule = '1 minute'
as
  call TASK_();

Even this task didn't work. Don't understand where I'm doing wrong

I wrote a task that calls the procedure every weekend.

Here is my procedure that inserts the values into a table

CREATE OR REPLACE TABLE TABLE1(DATABASE_ VARCHAR, TABLE_ VARCHAR); // Table to store values

CREATE OR REPLACE PROCEDURE TASK_()   //Procedure to insert values into table
    RETURNS string
    LANGUAGE JAVASCRIPT
    AS
    $$
        var str = '';
        var stmt = snowflake.createStatement({sqlText: "INSERT INTO TABLE1 VALUES ('DB1','TB1')"});
        stmt.execute(); 
        return str;
    $$;

Here is my task that calls the above procedure every weekend.

CREATE TASK mytask_hour
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = 'USING CRON 0 0 * 1-12 SUN America/Los_Angeles'
  TIMESTAMP_INPUT_FORMAT = 'YYYY-MM-DD HH24'
as
  call TASK_();

But when I checked, the above task didn't run and the values were not inserted into the table.

So, I tried to debug my task and wrote a task that calls the above procedure every minute.

create task mytask_hour
  warehouse = COMPUTE_WH
  schedule = '1 minute'
as
  call TASK_();

Even this task didn't work. Don't understand where I'm doing wrong

• javascript
• sql
• stored-procedures
• snowflake-cloud-data-platform

• How did you check whether the task run? – Marcel Commented Oct 5, 2020 at 19:09
• @Marcel I checked by querying the table which gave me an empty table – R0bert Commented Oct 5, 2020 at 19:45
• @R0bert adding one more link for troubleshooting tasks, check this docs.snowflake./en/user-guide/tasks-ts.html – Monem_منعم Commented Oct 6, 2020 at 2:02

2 Answers 2

Creating a task is not enough. The next step is to resume it as tasks are having the status "suspended" by default.

In your case the statement is

ALTER TASK mytask_hour resume;

Consequence: The tasks runs to your schedule.

On top of that you have to keep in mind that

• resuming/suspending a task requires OWNERSHIP or OPERATE privilege on the task
• the OWNERSHIP-role has the EXECUTE TASK privilege, which can be assigned by ACCOUNT ADMIN

For more infos see here: https://docs.snowflake./en/sql-reference/sql/alter-task.html

After creating a task, you must execute

ALTER TASK … RESUME

https://docs.snowflake./en/sql-reference/sql/create-task.html