Issue when i try to run mutliple line in a task

[Francois-lenne]

Description

When i try to run a a task with multiple line i have this issue :

snowflake.connector.errors.ProgrammingError: 001003 (42000): SQL compilation error: syntax error line 9 at position 63 unexpected '<EOF>'.

To Reproduce Steps to reproduce the behavior:

This is my code (my variable ENV work i'm 100% sure)

set sql_delimiter='/';

create or replace task TECH_{{ENV}}.TECH.TASK_ALIM_DWH_HIST
    warehouse=WH_ALIM_HP
    after TECH_{{ENV}}.TECH.TASK_ALIM_DWH
    as 

    BEGIN

        let ts_traitement timestamp_ntz := current_timestamp();
    --let  ts_traitement timestamp_ntz := to_timestamp('2024-06-01 14:00:00');

        use database DWH_{{ENV}};

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_ASSO_LOT_PROPRIETAIRE_HIST','FAITS','F_ASSO_LOT_PROPRIETAIRE','FAITS','ID_ASSO_LOT_PROPRIETAIRE');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_BAIL_LOCATION_HIST','FAITS','F_BAIL_LOCATION','FAITS','ID_BAIL_LOCATION');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_BATIMENT_HIST','FAITS','F_BATIMENT','FAITS','ID_BATIMENT');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_CLIENT_HIST','FAITS','F_CLIENT','FAITS','ID_CLIENT');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_CONTRAT_COPRO_HIST','FAITS','F_CONTRAT_COPRO','FAITS','ID_CONTRAT_COPRO');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_FICHE_METIER_COPRO_HIST','FAITS','F_FICHE_METIER_COPRO','FAITS','ID_FICHE_METIER_COPRO');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_FICHE_METIER_GESTION_HIST','FAITS','F_FICHE_METIER_GESTION','FAITS','ID_FICHE_METIER_GESTION');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_FICHE_METIER_LOCATION_HIST','FAITS','F_FICHE_METIER_LOCATION','FAITS','ID_FICHE_METIER_LOCATION');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_IMMEUBLE_HIST','FAITS','F_IMMEUBLE','FAITS','ID_IMMEUBLE');  

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_LOT_HIST','FAITS','F_LOT','FAITS','ID_LOT');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_MANDAT_GESTION_HIST','FAITS','F_MANDAT_GESTION','FAITS','ID_MANDAT_GESTION');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_SATISFACTION_HIST', 'FAITS', 'F_SATISFACTION', 'FAITS', 'ID_SATISFACTION');

        call TECH.ALIM_DWH_TABLE_HIST(:ts_traitement, 'F_TIERS_HIST', 'FAITS', 'F_TIERS', 'FAITS', 'ID_TIERS');
    END;/

Expected behavior

In snowsight the code is running and create the task

Schemachange (please complete the following information):

schemachange version: 3.7.0

[AMcNeice]

@Francois-lenne: https://github.com/Snowflake-Labs/schemachange/issues/171#issuecomment-1641109128

I ran into the same issue the other day.

Putting the multi-line sql into a proc and then calling it in the task was the workaround that we had to do. In your case, could wrap all those calls into a single proc (that calls the host of them) and then call that wrapper proc in the task.

[Francois-lenne]

@AMcNeice

Hello Andy yes i also think about this solutions i try the synthax for snowsql also but still the same error. The others solutions is probably to use the git integrations to snowflake and do an execute immediate command in a task

[ZFielden]

Just want to mention we are also seeing this issue. Hope a fix comes soon.

[sanelson]

I haven't tested this with schemachange yet but the following example from the bottom of the Snowflake EXECUTE IMMEDIATE documentation page helped resolve a similar issue I had running SQL scripts with the snow cli. I believe any snowflake tool using the python connector on the backend and executing a query via execute_stream or execute_string will run into this problem.

EXECUTE IMMEDIATE $$
DECLARE
  rs RESULTSET;
  query VARCHAR DEFAULT 'SELECT * FROM invoices WHERE price > ? AND price < ?';
  minimum_price NUMBER(12,2) DEFAULT 20.00;
  maximum_price NUMBER(12,2) DEFAULT 30.00;
BEGIN
  rs := (EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price));
  RETURN TABLE(rs);
END;
$$
;

TLDR; wrap your code block with...

EXECUTE IMMEDIATE $$

....

$$
;