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.
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"();