question

AdityaRaj-8613 avatar image
0 Votes"
AdityaRaj-8613 asked MartinJaffer-MSFT answered

Getting Error In Lookup while executing Stored Procedure For Snowflake

CREATE OR REPLACE PROCEDURE schema.merge_proc(SRC_TABLE varchar, tgt_table varchar, unique_columns varchar) RETURNS FLOAT not null LANGUAGE JAVASCRIPT EXECUTE AS CALLER AS $$ function getColumnsToBeInserted(difference) { difference_column = ""; for (i = 0; i < difference.length; i++) { difference_column = difference_column + 'target.'+ difference[i] + " = " +'src.' + difference[i] + "," } return difference_column.slice(0, -1) } function getJoinCondition(UNIQUE_COLUMNS) { var a = UNIQUE_COLUMNS.split(","), i; join_string =""; for (i = 0; i < a.length; i++) { join_string = join_string +'target.'+ a[i] + " = " +'src.'+ a[i] + "AND" } return join_string.slice(0, -3) } function getschema(schemasql) { var schema_arr = []; cmd1 = {sqlText: schemasql}; stmt = snowflake.createStatement(cmd1); var rs = stmt.execute();; while (rs.next()) { schema_arr.push(rs.getColumnValue(2)); } return schema_arr; } var row_count = 0; var src_table_arr = SRC_TABLE.split("."); var schemasql = "select ordinal_position as position,column_name,data_type from information_schema.columns where table_schema ilike '"+src_table_arr[0]+"'and table_name ilike'"+src_table_arr[1]+"' order by ordinal_position" var schemadetails = getschema(schemasql) var unique_columns_arr = UNIQUE_COLUMNS.split(",") var difference = schemadetails.filter(x => !unique_columns_arr.includes(x)); var column_values = (schemadetails.map(i =>'src.'+ i)).join(); sql_command = "merge into "+ TGT_TABLE + " as target using (select "+ schemadetails.join() +" from " + SRC_TABLE + " ) src on " + getJoinCondition(UNIQUE_COLUMNS) + " when matched then update set " + getColumnsToBeInserted(difference) + " when not matched then insert (" + schemadetails.join() + ") values (" + column_values +")" var stmt = snowflake.createStatement( { sqlText: sql_command } ); var res = stmt.execute(); res.next(); row_count = res.getColumnValue(1); return row_count; $$ ;

This is SP ,I am trying to execute using lookup activity. I am getting error:
Failure happened on 'Source' side. ErrorCode=UserErrorOdbcInvalidQueryString,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The following ODBC Query is not valid:

azure-data-factory
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I can see stored procedure being executed in snowflake but lookup is failing. I am not sure how to handle it.

0 Votes 0 ·

1 Answer

MartinJaffer-MSFT avatar image
0 Votes"
MartinJaffer-MSFT answered

Hello @AdityaRaj-8613 and welcome to Microsoft Q&A.

If I understand correctly, you are using Lookup Activity to execute a Stored Procedure. The Stored Procedure is running as expected, but Data Factory is throwing an error?

One possibility, is that no data is being returned.

The Lookup activity expects data to be returned. Not all Stored Procedures return data.

If your Stored Procedure does not return data, try appending a statement which does, such as Select 1;

Might that be the case?

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks . I changed it and ran it manually in snowflake and call stored procedure from adf. I am able to call it.

0 Votes 0 ·