Answered

Character Limitation in SQL Statement or Set variable ?

(0) Share

Report

Posted on by Niraj466

Hello,

I do have long query to execute through 'Execute SQL statement'. However, PAD is restricting me to insert the long query statement by showing 'Invalid value'. (I tried the same in 'Set variable' and getting the same issue.

This will work if I make the text too shorten.

Is there any way to insert the long data in 'SQL statement' or via 'Set variable'?

Categories:

Power Automate Desktop

I have the same question (0)

All responses (6)

Answers (2)

VJR 7,635 on at

Like (0)

Report

Hi @Niraj466

Can you check if it is a length issue or if the error is occurring because there are multiple INSERT INTO statements in the same box. If it can hold multiple statements does it need a separator or it cannot accept at all? That way you can figure out what solution you can implement.

Was this reply helpful? Yes No
Niraj466 73 on at

Like (0)

Report

Thank you VJR for responding.
I've already checked this by keeping two rows of INSERT Statements. It is working even though it is multiple INSERT statement.

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report

Alright, good, so length is not an issue.

Hope there are no syntax issues in the parameters of all the sql statements.

If that is fine then you will have to troubleshoot how many characters are allowed.

You will have to do this by removing the statements and checking whether its accepting or not.

For example 10 Insert into statements are not giving an error. Then add 1 and run and if that gives and error then that will tell the issue with the number of acceptable characters.

Then in that case you will have to run them in a loop with only acceptable number of statements.

Also by the way what are you inserting? Are they records from a spreadsheet? Are those insert statements into the same table or different table?

Was this reply helpful? Yes No
Niraj466 73 on at

Like (0)

Report

INSERT data will be coming from JSON format that I'm converting to custom object. Looping this custom object, I can make consolidated list for INSERT statement and that will be updated in SQL statement.
In looping the INSERT rows individually, it will execute the SQL for number of times and will also time taking process because the actual SQL is already break down in four pieces.

Let me think if can run it for group of 100 INSERT rows, if there will not be any other work around.

Was this reply helpful? Yes No
Verified answer

VJR 7,635 on at

Like (0)

Report

Convert the json to a datatable, then write that to an Excel or a text file and use a Bulk Insert sql command to insert all rows at once.

https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Bulk-Insert-from-Excel-to-SQl/m-p/1621380/highlight/true#M14888

Was this reply helpful? Yes No
Verified answer

Niraj466 73 on at

Like (0)

Report

Hi VJR,

Write to the text file and read the data from text file and insert in SQL statement as variable works now.

Thank you VJR for the solution provided here. I marked your solution as Accepted.

Was this reply helpful? Yes No