We are extracting data from a Date Base using an OLE DB connection for SQL server in Qlik Sense Enterprise.
At the beginning it was working well without any error, until we've started to experience this error:
Error: QVX_UNEXPECTED_END_OF_DATA:
Execution Failed
Execution finished.
The error pops up randomly when the code is reading any table, most of the time reading one of the biggest tables.
We thought it was because of the copies the client is making to the BD but even if we've asked to stop copies in a window of time of 1 hour and half, the error persists.
If you're using Qlik Sense on Windows, then watch the server resources during the reload. It's possible you are starting to max out your ram at peak points of the load. If you are using joins on any large data sets, I'd specifically watch during those operations. The final size of the application may appear much smaller than the size can get during loading so it's worth a shot.
This is one of the worst errors and it has been an issue forever. I've been doing qlik development a long time and I recall seeing it all the way back to 2009. The only way to fix this is to reduce the size of the data you are pulling. It can be either the number of rows or the number of columns. I suggest figuring out some kind of a partition strategy so you load smaller chunks and then save to individual qvd files.
Hi! The QVX_UNEXPECTED_END_OF_DATA error in Qlik Sense generally occurs when there is an unexpected interruption in data extraction, which could be caused by various factors like network issues, resource limitations, database locks, or query timeouts.
First I would check the network: Since the error appears randomly, ensure that there is a stable network connection between the Qlik Sense server and the SQL server. Network interruptions can cause data extraction to fail unexpectedly.
Then if the error occurs mostly with larger tables, try breaking down the data extraction into smaller, more manageable chunks. For example, use incremental data loads or limit the number of rows fetched in a single query. This can be done using filters like date ranges or row count limits. Check also the query timeout settings in both the OLE DB connection and the SQL server. Increase the timeout value to give the extraction process more time to complete, especially for larger tables.
Eventually I would try to optimize SQL queries used to fetch data. Ensure that indexes are correctly set up on the SQL server for the columns being queried to speed up the data retrieval process.
The issue is that data are not that much yet. We have the same DB in another client (where we are extracting more tables and data) and this problem doesn't occur.
For extracting data we are making partitions and saving each table in a QVD file, but sometimes just the extraction of one single table can fail.
We are thinking the problem is network connection between the QS server and the SQL server, as @diegozecchini suggested.
We have also appreciated that, randomely, the extraction of huge tables can be fast, although it is slow most of the time.
How could we test the connection between QS and SQL server? We know about the test.udl file, are there more options?
Hi! using the test.udl file is one way to test the database connection, you can go further by using some additional tools and methods to diagnose the issue. For example, you can use the ping command to check basic connectivity and latency between the Qlik Sense server and the SQL server. If latency is high or there are timeouts, it could indicate network instability.
For a more detailed network analysis, you might want to use tracert (or traceroute on Linux) to trace the path packets take to reach the SQL server. This can help identify any bottlenecks or delays along the route. Additionally, tools like telnet or nc can test specific port connectivity (e.g., SQL Server's default port 1433) to see if there are any restrictions or interruptions in network communication.
If you suspect fluctuations in network performance, you could run a continuous ping test (ping -t) over a period to monitor latency and packet loss. This will give you more insight into whether network stability is contributing to the issue.
Lastly, enabling detailed logging on the Qlik Sense and SQL servers can provide more information about connection drops, timeouts, or other anomalies during data extraction.
I used to have this error when using Qliksense enterprise as 85% of my connections are sequel and on OLE DB connections. Every morning I would log into qmc to study the errors - and nothing much - Unexpected end of data. Majorly, this occurred on tables and views in aws cloud and that could have been time outs. When I moved to qlik cloud, I changed all extractions to be run on Qlikview which saves the extracts on QVDs and push them to Qlik cloud using QDT.
I would recommend reviewing the way extraction is done. Use SQL optimizing techniques such as avoiding SELECT *, avoid any filtering, unions, joins and subqueries. There is only one that was hard to crack load on sql view which I converted to table. Thence, use the load editor on Qlik to script out and applying the filters through use of resident tables techniques and table drops. Additionally, you can have a staging folder in Qlik such that all extracts move to this folder then you have an app that performs the scripting and moving the final cleaned or proper qvd to main folder for use by the apps.
The same sad history of my mornings, logging into qmc to check the same unexpected end of data error 🙄.
Regarding your tips: no joins, no filtering, unions or subqueries, but yes we are extracting all fields (as we are making with the same SLQ DB in another client without any issue). We use the extraction app to generate and save QVDs in a folder, then we load and transform those QVDs in another app (regular ETL process).
We've experimented that if we disconnect, connect, extract and save QVDs more times, no error most of the time, but extracting from the original DB, 'cause it still fails if we extract from the replica and It is suppoded we have to extract from replica.
We are going to check the extraction in chunks from the original DB, 'cause replica seems to give always problems. If still problems most of the times, we will load just necessary fields and not alls. Keep you informed.
