2

I would like to know if there's any special requirement when calling a Snowflake stored procedure in a Informatica mapping. Concretely, I have a mapping in which the target is a snowflake table, and as Post-SQL, I want to call a stored procedure that is in the same database as my table.

I call my stored procedure in Post-SQL as following:
CALL spname();

However, I get the following error when running:
SQL compilation error: Unknown function spname

Do you know which could be the problem here?

Koushik Roy
  • 6,868
  • 2
  • 12
  • 33
Marta Piñol
  • 31
  • 2

2 Answers2

2

That error message is coming from Snowflake, so Informatica (is this PowerCenter on-prem?) is attempting to run the SP and it's getting a response back from Snowflake. Here are some things to check:

  1. Does the Snowflake user PowerCenter runs as have the required grants to run the SP? The error message will be the same whether the SP does not exist or the user lacks privileges to run it.
  2. Does the user running PowerCenter have the required grants on the database and schema containing the stored procedure?
  3. You can ensure that PowerCenter is looking in the right namespace by specifying both the database and schema before the SP name, such as call "MY_DB"."MY_SCHEMA"."MY_PROC"();
Greg Pavlik
  • 10,089
  • 2
  • 12
  • 29
0

I had the same issue, I didn't call like all "MY_DB"."MY_SCHEMA"."MY_PROC"(); I filled up the advance query options input fields one by one. And it worked for me.

parlad
  • 1,143
  • 4
  • 23
  • 42