OLEDB - Get collection

[Malligai]

Hi All,

When I tried to connect the OLEDB for one of my process, its working fine(its nearly 50 rows of data as output). But for another process its not working. while running its take sometime and throwing the error. (while manually the result is having only 10 rows output). its showing the below error when its reaching the get collection action.

Internal : Could not execute code stage because exception thrown by code stage: Exception of type 'System.OutOfMemoryException' was thrown.

I dont know what exactly, because its running with one process, and another process its showing the error.

Is anything i have to do? does it required any additional workout?

please help!!!

 

[RobertJ]

There could be any number reason you are having an out-of-memory error. In the past I have received a memory error when using the Excel VBO. In my case the spreadsheet was just too large, but if you believe that only 10 rows are to be returned it would seem not as likely that the data set would be "too large".

More details on your data source and data types would help.

 

[VJR]

(while manually the result is having only 10 rows output

10 or 50 rows shouldn't really be a problem. Are there tons of columns (instead of rows) by any case? If it is happening in this process only, then try retrieving records from another table but from the same process and see what you get. Are you running an SQL query, or a stored procedure, or using Oledb for an Excel sheet?

 

[Malligai]

Hi VJR am running with the SQL queries only. am facing with only 9 rows. i can get the result for another table also.

For this particular query only, its showing this error.

This code only used in our OLEDB object for get collection.

Dim oDataAdapter As New OLEDBDataAdapter(SQL,moConnection)

Dim oDataSet As New DataSet
oDataAdapter.Fill(oDataset)

Results = oDataSet.Tables(0)

Is anything i have to change in that?

NOTE : when am using the "OLEDB - Execute action", its executing successfully, only the problem is to get the collection.

 

[VJR]

For this particular query only, its showing this error.

So we have pinpointed the issue to this query only. Is there something that can be done to this query like SQL performance tuning? Can you take a deeper look at the query and see if it has something questionable like a query within query, inner/outer joins etc. Which backend database are you using? How much time does it take if/when manually run in SQL? Have you taken a look at the Execute action instead of the Get collection?

 

[Malligai]

VJR, Thanks for the time. its running manually within 0.015 seconds. and also its executing successfully.

to take into collection only, its not running. Actually the query was standard which i got from client. they are using inner query to find some logic based date. I have tried to insert those dates manually into values and removed that inner query. But my bad luck, its also not running.

 

[VJR]

VJR, Thanks for the time. its running manually within 0.015 seconds. and also its executing successfully.

to take into collection only, its not running. Actually the query was standard which i got from client. they are using inner query to find some logic based date. I have tried to insert those dates manually into values and removed that inner query. But my bad luck, its also not running.

Sounds strange. Can you tell me on Which backend database are you using?

 

[Malligai]

We are using Oracle DB

 

[VJR]

We are using Oracle DB

okay, you mentioned earlier about dates in the query. Check if using closest to today's date causes the same issue.
For example; if the query selects records between two dates, then check by using a smaller date range and gradually increase it in each run. That way you may either stumble upon something or may even pinpoint the actual problem.

Another option is to try to use the query in a stored procedure and then see if it produces the same result. That may possibly tell something.

If at last you see that there is absolutely no issue from the database side and you have exhausted all possible options check by raising a ticket with Blue Prism and notify them about your problem and the options you have already tried.

 

[Malligai]

i have tried with the way (way1), but still its not running. the exact same one i have pasted in the BP action as input, it showing right parenthesis missing (i have looked much time, but couldn't find the parenthesis error)

That way2 : am trying to enhance the query with procedure.

and thanks for the suggestion, will update once find the solution (or) anything needed.

 

[Prabhakar Yadav]

Pls solve this query on oledb of joining method