Problem Execute SQL Statement

[Wendy-Zheng]

Dear all,

i am using Power Automate Desktop to analyze excel data by SQL, but having problem when Execute SQL Statement:

after running the flow, there is no data in QueryResult (see highlight in below picture), is it normal? or is it a failure? i am not sure because there is no issue alert during flow running. could anyone give some suggestions? thanks in advance!

[Srini007]

Hi @Wendy-Zheng 

Can you check in the SQL table, whether you have the data or it is empty?

If data is empty then that is correct

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Thanks,

Srini

[Srini007]

Hi @Wendy-Zheng 

try writing query as SELECT * FROM TABLE1

For more reference you can check below link

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-microsoft-access

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Thanks,

Srini

[Srini007]

Hi @Wendy-Zheng 

Check below documentation for Excel file

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Thanks,

Srini

[Wendy-Zheng]

Hi Srini,

After check, the SQL Testing excel file has data like below (1st picture). So I tested the connection of SQL and it reported an error (2nd picture), is it the cause of empty QueryResult (no SQL connection)?

[Wendy-Zheng]

Thanks Srini,

I tried with this code and run the flow again. Similarly, there is no alert/error during flow running but the QueryResult is still empty.

[Srini007]

Hi @Wendy-Zheng 

Did you install the below Redistribtuable?

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Thanks,

Srini

[Wendy-Zheng]

Hi Srini,

Yes, I have installed it and the engine can be found in the Provider in Power Automate.

[Srini007]

Hi @Wendy-Zheng 

1) Check the connection string is as below

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties="Excel 12.0 Xml;HDR=YES";

2) In Execute sql statment write as 

SELECT * FROM [SheetName$]

SheetName->Replace that with your excel sheet Name

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Thanks,

Srini

[Gidi]

Hi,

seems you are on the right path (Configure a connection string manually)

Do you really have blank password for User ‚Admin‘?

Get the last error action result for more details of the problem.

Try further with Error Handling of the used actions to find the root cause. 

[Srini007 · Accepted solution]

Hi @Wendy-Zheng 

Tried this below are the steps

1) Installed the Redistruable of 64x

2) Copied the connection string and it worked

You copy the steps below and paste to the PAD

SET Excel_File_Path TO $'''D:\\PRODTriggers.xlsx'''
Database.Connect ConnectionString: $'''Provider=Microsoft.ACE.OLEDB.12.0;Data Source=%Excel_File_Path%;Extended Properties=\"Excel 12.0 Xml;HDR=YES\";''' Connection=> SQLConnection
Database.ExecuteSqlStatement.Execute Connection: SQLConnection Statement: $'''SELECT * FROM [Triggers$]''' Timeout: 30 Result=> QueryResult

If you find this reply helpful, please consider giving it a LIKE AND

If this reply answers your question or solves your issue, please ACCEPT AS SOLUTION

Thanks,

Srini

[Ravi14]

Hi Wendy,

Have you checked manually whether you are getting any results or not?

[Wendy-Zheng]

Hi Srini,

I followed the instructions that you shared previously but it seems not work.

https://learn.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel

Thanks and Regards

Wendy

[Wendy-Zheng]

Hi Ravi,

I tried to use Microsoft Query directly and it could get correct results. Therefore, I think the problem may be the connection to SQL. Thanks.

[Wendy-Zheng]

Hi Gidi,

It is the default selection when I Open SQL Connection in Power Automate, and I made no changes in it. Thanks.

[Ravi14]

Hi Wendy,

Can you please share the error SS which you are getting exactly

[Wendy-Zheng]

Dear all,

Thank you so much for your kind help and good ideas, my flow is now working smoothly!

Best Regards

Wendy