search

microsoft / PowerPlatformConnectors

This is a repository for Microsoft Power Automate, Power Apps, and Azure Logic Apps connectors
https://aka.ms/connectors
MIT License
975 stars 1.26k forks source link

Snowflake connector in Power Automate issue #2296

Open Jack-Currie opened 1 year ago

Jack-Currie commented 1 year ago

Type of Connector

Certified Connector

Name of Connector

Submit SQL Statement for Execution (Snowflake)

Describe the bug

I am getting the following error when trying to use the connector in power Automate.

"The API 'snowflakeip' returned an invalid response for workflow operation 'Submit_SQL_Statement_for_Execution' of type 'OpenApiConnection'. Error details: 'The API operation 'ExecuteSqlStatement' requires the property 'body' to be of type 'Object' but is of type 'String'.'"

I am simply adding the information as required, so the instance, writing the required SQL statement in the statement section etc .

Can you advise how i can resolve this?

Is this a security bug?

No, this is not a security bug

What is the severity of this bug?

Severity 1 - Connector is broken or there is a Serious issue

To Reproduce

Select Submit SQL Statement for Execution from power automate actions

add the instance app.snowflake.com/north-europe.azure/inidividualidentifier

Statement : SELECT ACTIVITYID FROM ANALYTICS.DYN.EMAIL

Database: ANALYTICS

Schema: ANALYTICS.DYN

Expected behavior

connection should run successfully without producing error

Environment summary

Install Method (e.g. pip, interactive script, apt-get, Docker, MSI, edge build) CLI version (paconn --version) OS version Shell Type (e.g. bash, cmd.exe, Bash on Windows)

Additional context

Same error is occurring for the other actions provided by this connector.

IlyaAspirent commented 1 year ago

Were you able to resolve this issue? I am getting the same response.

nicktimmsdg commented 1 year ago

I am also receiving this response. Have tried a variety of different Instance, statement, and integration changes.

IlyaAspirent commented 1 year ago

I was able to figure out what the issue was on my end. I was doing a Copy Into SQL statement so I wasn't expecting any results back from the query. Because the connector is built to retrieve data, it ended up failing. To work around this, I created a snowflake Stored Procedure that generates a file name uploaded as the result of the query. This at least allowed me to avoid this error. Not sure if this is the case for anyone else, but I did end up opening a ticket to allow for result-less sql statements.

nicktimmsdg commented 1 year ago

I was able to figure out what the issue was on my end. I was doing a Copy Into SQL statement so I wasn't expecting any results back from the query. Because the connector is built to retrieve data, it ended up failing. To work around this, I created a snowflake Stored Procedure that generates a file name uploaded as the result of the query. This at least allowed me to avoid this error. Not sure if this is the case for anyone else, but I did end up opening a ticket to allow for result-less sql statements.

Thanks for the reply... Mine is not working with a simple select statement, but the use case I'm working towards does involve insert/update/deletes. Im assuming its a connection issue, but not able to get any helpful error text....

IlyaAspirent commented 1 year ago

Nevermind. I am now getting it with any select statement. I opened a support ticket through Microsoft. Something changed because none of my flows using snowflake are working.

esbasti commented 8 months ago

For me, it was a slash at the end of the Instance URL

Universezero commented 7 months ago

I was able to fix this error by changing my instance uri from: app.snowflake.com/ORGNAME to ORGNAME.REGION.azure.snowflakecomputing.com