SQL database error

[Fernando84]

Hill everyone, I run a process everyday to insert data into ORACLE DB from Geo SCADA, but today I got this error (attached image). Even I tried to insert the data manually but I'm sitll getting the same error. Any suggestion?

[AndrewScott]

The "Size limit exceeded" error indicates that the result set of your SQL query is too large, i.e. it using too much memory. You should review the query to ensure it isn't returning more data than expected.

The maximum size (default 250MB) can be changed, see Use the Parameters Settings to Define the SQL Query Row and Size Limit but you need to be careful about running out of memory or adversely effecting the performance of the system.

[Fernando84]

Hi Andrew, Is there any way to know the size (MB) of my query?

[AndrewScott · Accepted solution]

The Query Processor diagnostics don't show the size, but it is logged in the DB log. Look for a line like the following (with "Resultset complete") which shows the number of rows and size for the result set:

20-APR-2023 15:44:58.432 2B30 [SCX] L09#01 QPTable 2 Resultset complete (47 rows, 10152 bytes)

[Fernando84]

Thanks for your recommendation Andrew, I'm gonna check the log. 

[Fernando84]

Hi Andrew, I already checked the DB log but the maximum data size I found is 217 rows 232624 bytes. But I found another line indicating about engineering units exceeded (image). Can this cause the error?

[AndrewScott]

The "exceeded.jpg" log shows a request from an OPC DA client (e.g. a mimic in ViewX) for 62 items where some of these items (probably points) are outside the engineering range for the item (e.g. overrange or underrange analogue points). This is totally unrelated to SQL queries that are too large.

You should be able to find entries in the DB log based on the timing or looking for the query that your process executes which is failing with the "Size limit exceeded" error (the SQL itself is in the DB log).

If your system has multiple servers make sure to look at the DB logs for the server your process has connected to, which may not be the current main server.