Snowflake script through ODBC not returning data

[jamesly]

I created a SQL script to dynamically change a table's column names to its column comments.

Here is my code:

sql1 <- sql(
  "
  DECLARE
      res RESULTSET;
      query_string VARCHAR;
      tbl VARCHAR;
      tbl_sch VARCHAR;
  BEGIN
      tbl := 'V02_SAP_TVOS';
      tbl_sch := 'DRTRV02';
      query_string := CONCAT(
        'SELECT ',
        (
        SELECT
          LISTAGG(COLUMN_NAME || ' AS \"' || COMMENT || '\"', ', ')
                  WITHIN GROUP (ORDER BY ORDINAL_POSITION) AS dynamic_query
          FROM
              INFORMATION_SCHEMA.COLUMNS
          WHERE
                TABLE_NAME = :tbl
            AND TABLE_SCHEMA = :tbl_sch
            ),
            ' FROM ', :tbl_sch, '.', :tbl
        );
  
        res := (EXECUTE IMMEDIATE :query_string);
        RETURN TABLE(res);
  END;
"
)

myconn <- 
  DBI::dbConnect(
    odbc::odbc(),
    Driver = "SnowflakeDSIIDriver",
    Server = "xxxxx.snowflakecomputing.com",
    Authenticator = "externalbrowser",
    uid = "yyyyy",
    database = "dev_main"
  )

DBI::dbGetQuery(myconn, sql1)

I can run this query on app.snowflake.com no problem but in R, this result in an empty dataframe (0x0). I can query normal SELECT statements no problem in R.

What's going on here? Is there something I need to do to the Snowflake ODBC driver parameter to be able to use scripts?