Calling a stored procedure stored in Snowflake from Script Activity in ADF

Question

When I try to execute a stored procedure in Snowflake from a Script Activity in Azure Data Factory, I got this error:

ERROR [42000] SQL compilation error:
Syntax error line 1 at position 15 unexpected 'DCA_TEST1_DB'.

I have successfully configured a Snowflake linked services.

I put this code in the script:

@concat('CALL PROCEDURE DCA_TEST1_DB.DB_TEST1.UpdateSalesforceAccount();')

I have tested the procedure is running OK.

I'm not sure if I can use a script activity for this.

Answer 1

I got the solution!

In the stored procedure in Snowflake, write the objects with double quotes, like this:

CREATE OR REPLACE PROCEDURE DCA_TEST1_DB.DB_TEST1.UPDATESALESFORCEACCOUNT()
  RETURNS STRING
  LANGUAGE SQL
  EXECUTE AS CALLER
AS
$$
BEGIN
  -- SQL statement to update the "Name" field in the salesforce_account table
  UPDATE "DCA_TEST1_DB"."DB_TEST1"."SALESFORCE_ACCOUNT"
  SET "Name" = 'Name_' || CAST(RANDOM() * 10 AS STRING);
  
  RETURN 'Success';
END;
$$;

In script activity, call the procedure including double quote like this:

CALL "DCA_TEST1_DB"."DB_TEST1"."UPDATESALESFORCEACCOUNT"();

enter image description here