topic Re: Error: QVX_UNEXPECTED_END_OF_DATA: in Connectivity & Data Prep

Error: QVX_UNEXPECTED_END_OF_DATA:

AliceB — Mon, 16 Sep 2024 11:01:28 GMT

Hi all.

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.

Any idea what is happening and how to fix it?

Thanks in advance.

Alice

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

mshann01 — Wed, 25 Sep 2024 18:39:33 GMT

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.

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

AliceB — Wed, 25 Sep 2024 18:53:24 GMT

Thanks @mshann01 for replying.

- We have already checked the server RAM usage during the extraction and it is still very low.

- On the other side, we are not using joins, we are just extracting tables.

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

chriscammers — Wed, 25 Sep 2024 21:32:01 GMT

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.

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

diegozecchini — Thu, 26 Sep 2024 06:31:00 GMT

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.

Hope it helps
regards

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

AliceB — Fri, 27 Sep 2024 08:13:02 GMT

Thanks @chriscammers for replying.

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?

Thanks again @diegozecchini and @chriscammers.

Alice

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

diegozecchini — Fri, 27 Sep 2024 09:02:42 GMT

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.

hope it helps
Regards
Diego

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

AliceB — Fri, 27 Sep 2024 09:10:30 GMT

Many thanks @diegozecchini.
I will keep you post it about the connection tests.

Regards,
Alice

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

kismoflans — Sun, 29 Sep 2024 12:25:53 GMT

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.

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

AliceB — Mon, 30 Sep 2024 07:46:33 GMT

Thanks @kismoflans for the answer.

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.

Alice

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

Anonymous — Mon, 30 Sep 2024 09:06:49 GMT

Hello @AliceB , many thanks for sharing your case and following it up; I believe the case is really well described; I would be happy to help you but - from my point of view - I belive more logs and informations are requiered here.

What version of SQL SERVER are you using? What version of QLIK are you using? What version of OLE DB driver are you using?

When the above error appears on the client side, can you check for server-side logs, if any?

From a server-side perspective, do you have any logs related to your error when it appears on client-side?

Do you have more detailed logs from a client-side perspective?

Regards,

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

chriscammers — Mon, 30 Sep 2024 12:42:55 GMT

Here is what I would suggest

Query the table directly do not use a view
simplify your code, remove joins, subqueries anything other than select...from where...
apply any where clause to the sql part of your load statement
Reduce the fields retrieved to only the ones you are using, do this in a methodical fashion so you can determine if any individual fields are causing the issue.
Reduce the number of rows retrieved in a single query, partition by some meaningful field to split large tables into more manageable segments
test your network latency and take steps to reduce
- In the cloud - gateways and QDTU servers need to have close proximity to the database server.
- Client Managed servers - reload nodes should also be proximal to the source data

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

AliceB — Fri, 04 Oct 2024 07:21:22 GMT

Hi all.

There are some news.

We've created a new extraction APP using a Microsfot SQL server connector (we've been using the OLEDB SQL Server Connector until now) and the error is this one: Error: ERROR [08S01] TDSConnectionFailed.
We experiment the error at 3.30 AM when the copies of replica DB are stopping and supposely there are no other DB processes or QMC reloads, but it works after a couple of intents at 7.30 AM.

Just to answer to @chriscammers :

We are trying to extract from the orginal DB and from its replica too. Both give us same errors.
No joins or subqueries, just extraction of whole tables.
We use where in a few tables when it's required to limit the rows by date or type.
We are selecting all the fields as we make in the other client (the error doesn't occur in the same tables, but most of the time extracting the biggest ones).
We have divided the code in more sections where we connect to the DB, exctract and save tables in QVDs, finally disconnect and reconnect another time.
We are not using QS Cloud but QS Enterprise, and we just have one node.

Don't know how to manage it this error of TDSConnectionFailed.

Thanks in advance to all,

Alice B.

Re: Error: QVX_UNEXPECTED_END_OF_DATA:

kismoflans — Mon, 07 Oct 2024 07:27:38 GMT

@AliceB I have another suggestion. Create a folder on the server where Qliksense can read files from. Download QDT and Qlikview. Create your odbc ole connections on Qlikview. Paste your sql script on the load editor and store the table to QVD to the folder that Qliksense can access files from. Use QDT to schedule times for the Qlikiew to run extraction.