schemachange gives <EOF> error executing DDL for tasks with multiple statements wrapped by !set_delimiter

[rbusch45]

I tried multiple syntactical approaches for creating tasks with multiple SQL statements in a migration script. This example from Snowflake docs does not work:

!set sql_delimiter=/ CREATE OR REPLACE TASK METADATA.ROOT_TASK WAREHOUSE = COMPUTE_WH WHEN SYSTEM$STREAM_HAS_DATA('METADATA.TRIGGER_STREAM') AS BEGIN call METADATA.INIT_ETL_RUN('Y'); END; !set sql_delimiter=";"

The work around I found is this:

CREATE OR REPLACE TASK METADATA.ROOT_TASK WAREHOUSE = COMPUTE_WH WHEN SYSTEM$STREAM_HAS_DATA('METADATA.TRIGGER_STREAM') AS EXECUTE IMMEDIATE $$ BEGIN call METADATA.INIT_ETL_RUN('Y'); END; $$ ;

is/will !set_delimiter (be) supported by schemachange?

other suggestions?

[tsceurman-ara]

schemachange seems to prefer the older syntax for Snowflake scripting, probably because of the connector, so you have to use the EXECUTE IMMEDIATE to get it to work.

That said, why are you wrapping the stored proc call in a BEGIN/END? You do not have to do that in the task. I did find that when I have a CALL statement, it cannot end with a semicolon or a get an error when deploying the code via schemachange; not sure why that is though. So your Task code would look like this in the script:

CREATE OR REPLACE TASK METADATA.ROOT_TASK WAREHOUSE = COMPUTE_WH WHEN SYSTEM$STREAM_HAS_DATA('METADATA.TRIGGER_STREAM') AS call METADATA.INIT_ETL_RUN('Y') ; That's what has been working for us, at least.

[rbusch45]

This was a simple example I constructed to demonstrate the syntax problem for this issue. In the real world, I have multiple statements that need to be called by each task that I did not want to push down to the stored procs being called by each task, (logging scaffolding) therefore the need for the BEGIN - END

[sfc-gh-jhansen]

Hey all. The primary issue here is that !set sql_delimiter is a snowsql specific thing. Did the advice @tsceurman-ara gave help?

[rbusch45]

Our workaround is working fine. You can close this issue.