How to handle database with more than 16,777,216 row in uipath

Help Studio
,
1

I am trying to fetch data from sql database using ‘Execute Query’ activity and capturing the result in datatable. It was working fine till yesterday. For some sql queries, UiPAth was stopping execution abnormally with below error message
“RemoteException wrapping System.Exception: Job stopped with an unexpected exit code: 0xE0434352”

This happened even if I had tried to catch the exception using Try Catch.

On further analysis , I found that the sql query that was run was returning around 30,822,870 rows. But Datatable can handle only 16,777,216 rows.

Can some one help to solve on how to handle this situation as even try catch is not handling this

1 Like
2

@ppr, @Palaniyappan, @loginerror

1 Like
3

Hi

Yes that’s right. Datatable can handle only till 16,777,216 rows

And if we want to process more than that we can limit till that count and store as one datatable with LIMIT statament
And fetch the remaining again as a another datatable and process it.

Even excel stores only 1048576, so we can do it on splitting the dataset

And for this, you would have run the workflow on Debug mode. Press ctrl+F6 or Click Run File or Run Project to run in normal mode

Hope this would help you

Cheers @nabeelu

1 Like
4

Hi @Palaniyappan

I had tried in running the file (not in Debug mode). Still this exception ( “RemoteException wrapping System.Exception: Job stopped with an unexpected exit code: 0xE0434352”) was not caught. I tried giving System.Exception to catch this one
Any other way to catch this?

Also another query on Limiting number of records. As per my workflow I have many sql queries in an excel sheet. My bot takes each query and executes using ‘Execute Query’ activity. Only once in a while my database has more than 16,777,216 records for a certain query. How can I use Limit only in such situation,

5

@nabeelu
as mentioned by @Palaniyappan there are limits
in such cases we do have 2 options:

  • staying in the UiPath Box: handling with splits
  • integrating an outside approach into the UiPath world

for second let me elaborate a more on details & ideas:
in some cases tasks will be done different (other tools, tech stacks …). But with an integration / binding to the UiPath Bot we do not lose control/automation pervasion. In short it does mean. When the BOT can trigger/invoke additionals then we can handle within UiPlattform as well (status, reporting, retries …) and this area is not lost in the automation approach

6

Sorry , I didn’t get how to implement this.

Suppose for the first option- how can we implement this. As unless we run ‘Execute Query’ activity we don’t know the database count. My database displays this count only for very less queries. But Execute query throws out above error and UiPath stops when count is more than 6,777,216

7

Hi,

You Can implement the SQL paging concept into sql query .

Refer the SQL part URL: Introduction to pagination in SQL Server

Setp 1:Get the total Rows count from Table
Step 2: Load the top N number of rows using execute non query “top 1,000,000” by passing start
& End number of Rows, (For this step 0, 1000000)
Step 3: Load the data into data table.
Step 4: Do your logic
Step 5: Repeat the step from 2 by changing start & End Row values based on the Total number of rows.

3 Likes
8

The answer is simple, just dont handle 16M rows in any program you develop, it does not make any sense, that is why we have servers to hold data outside of the applications… Bring only the data your process will use in that run.

9

Hello All, I understand that this is an old topic however, I am having this issue with a SQL Query that appears when the output is only 20k rows. I am using StudioX, are the limitations for StudioX different?

This error only appears when an output is listed in the Execute Query activity. When I leave the output blank, the action completes.

Please advise on how to get the output from the query.

10

Hi @NatesMom

Does it work properly for you in studio?