schema does not exist and not authorized

user_17218115 0
Viewed 707

I am trying to create a Procedure in snowflake.

CREATE OR REPLACE PROCEDURE test()
returns string not null language javascript as
$$ 
    var cmd = "select count(1) from Test1.table1";
    var sql = snowflake.createStatement({sqlText: cmd});
    var result = sql.execute();
    return '1'; 
$$;

"table1" existing in schema "schema1" but i am trying to create this procedure in schema2. schema2 does have an access to "table1.

when I run the same query in snowflake web UI with schema "schema2" where i am creating the procedure select count(1) from Test1.table1 it is working but inside procedure it is not working and displaying me error

schema does not exist and not authorized

CREATE OR REPLACE PROCEDURE test() 
  returns string not null language javascript as 
 $$ 
 var cmd = "select count(1) from Test1.table1";
 var sql = snowflake.createStatement({sqlText: cmd});
 var result = sql.execute();
 return '1';
 $$;
snowflake-cloud-data-platform
1 Answers

if you fully qualify you names, things just work:

use schema test.test;

create schema test.test1;
create table test.test1.table1(id int);

CREATE OR REPLACE PROCEDURE test.test1.test()
returns string not null language javascript as
$$ 
    var cmd = "select count(1) from test.test1.table1";
    var sql = snowflake.createStatement({sqlText: cmd});
    var result = sql.execute();
    return '1'; 
$$;

call test.test1.test();
TEST
1 
create schema test.test2;

use schema test.test2;

call test.test1.test();
TEST
1 
use schema test.test2;

CREATE OR REPLACE PROCEDURE test.test2.test()
returns string not null language javascript as
$$ 
    var cmd = "select count(1) from test.test1.table1";
    var sql = snowflake.createStatement({sqlText: cmd});
    var result = sql.execute();
    return 'called table1 from schema2'; 
$$;

call test.test2.test();
TEST
called table1 from schema2

So why your error?

The below SQL I have made non fully qualified. So the function will be in the current scehma. Which for me is test.test2. But now I am referring to schema schema1.table1 and schema1 does not exist. thus the error message when I run the code.

CREATE OR REPLACE PROCEDURE test()
returns string not null language javascript as
$$ 
    var cmd = "select count(1) from schema1.table1";
    var sql = snowflake.createStatement({sqlText: cmd});
    var result = sql.execute();
    return 'called table1 from schema2'; 
$$;

call test.test2.test();

gives:

SQL compilation error:

Schema 'TEST.SCHEMA1' does not exist or not authorized.

At Statement.execute, line 4 position 21

The other possible outcome is you have the function defined as EXECUTE AS OWNER and the two functions do have two different owners, with different owning permissions. But I am going to doubt that.

user_439920
Related