cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RahulRami
Regular Visitor

How to Use Sql variable within desktop variable in Execute Sql Query action

Hi ,

I am trying to retrieve a data from datatable type variable for specific index and try to insert data into table, but some how i am not able to do it.

SQL Code :
==============================
Declare @Ccount int
Set @Ccount = 0
while (@Ccount < %NewVar%)
begin
Set @Ccount = @Ccount + 1
Insert into "TTable" Select %ExcelData[@Ccount]%
end
================================

here 
1. @Ccount is SQL variable 

2. %ExcelData%  power Automation Desktop Datatable variable 

i am trying to retrieve data from specific index from datatable variable %ExcelData[@Ccount]% 
but it not working throw Syntax error message.

RahulRami_0-1622455684520.png

 


please do a need full help. 

10 REPLIES 10
Sonic
Frequent Visitor

Hi @RahulRami , did you manage to resolve this?Im asking because i basically have the same issue.

Sonic
Frequent Visitor

Hi @RahulRami , did you manage to resolve this?Im asking because i basically have the same issue.

RahulRami
Regular Visitor

Hi @Sonic ,
Sorry to say but, I can't find any solution regarding this. 

kostasc
Microsoft
Microsoft

Hey @RahulRami,

 

The issue is that Power Automate Desktop's engine can't evaluate the index (@Ccount) inside the ExcelData datatable. 

 

Could you please try this approach instead?

 

kostasc_0-1628582821786.png

 

 

The execute SQL statement action should look like this:

 

kostasc_1-1628582867889.png

 

 

Shin12
Frequent Visitor

Hi! 

i know this is already old question ,i just want to answer about SQL query in Power Automate Desktop

 

I hope when someone stumble same problem  and accessed this post can see it.

 

the problem for variable inside query is that variable are written like this %yourvariable%.

 

% is the problem here, you need to add double quotation to escape it like "%yourvariable%"

 

please see more details over here

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

 

have a nice day

I tried your suggestion but am getting Sql statement: Syntax error when using the following sql. Any ideas on how to fix? I've tried replacing single with double and vice versa. 

 

SELECT XPPR.CHECK_NO, 
  TO_CHAR(XPPR.VALUE, '99999999D99') CHECK_AMT, 
  XPPR.SELECTED
FROM XXCA_PR_PAYMENTS_REC XPPR
WHERE 1=1
  AND XPPR.EFFECTIVE_DATE <= NVL('', '31-DEC-9999')
               AND XPPR.PROCESS_NAME = 'Check'
  AND XPPR.CHECK_NO IN ("%CurrentRow[Tran Detail Chk No]%")
ORDER BY XPPR.CHECK_NO, XPPR.EFFECTIVE_DATE, XPPR.EMPLOYEE_NAME

Below query worked for me where InputProcessType is a variable in PAD and ProcessType is an SQL column containing text.

Note the enclosed single quote in red.

SELECT *   FROM MytblName where ProcessType = '%InputProcessType%'

 

If still not working add the entire sql query in a variable and print it in a displaybox to see what's going wrong with the syntax.

 

 

Here is your code:

SELECT XPPR.CHECK_NO, 
  TO_CHAR(XPPR.VALUE, '99999999D99') CHECK_AMT, 
  XPPR.SELECTED
FROM XXCA_PR_PAYMENTS_REC XPPR
WHERE 1=1
  AND XPPR.EFFECTIVE_DATE <= NVL('', '31-DEC-9999')
               AND XPPR.PROCESS_NAME = 'Check'
  AND XPPR.CHECK_NO IN ('%CurrentRow['Tran Detail Chk No']%')
ORDER BY XPPR.CHECK_NO, XPPR.EFFECTIVE_DATE, XPPR.EMPLOYEE_NAME

 

The bold part was the issue. If you're naming the column in your row, the name needs to be put in quotes.
%CurrentRow["Column Name"]%

Thank you. I will give that a try and let you know!

Wakeley
Regular Visitor

Thanks. 

I had the same issue, but quotes solved it.
Fx: 

VALUES ('%CustomFormData['TI Product Name']%','%CustomFormData['TI Product Description']%)

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (4,031)