You may want to check into the Connection String in the Qlik Data Connection - Google up on what what parameters to use (make sure to note whether the value is in milliseconds or seconds...). It may tie to an ODBC setting you're using. You may be able to enter the parm in the Adavanced (Name / Value) section, or if you have access to the QMC in the Data Connections (where you'll find the Connection string as something like this "CUSTOM CONNECT TO "provider=QvOdbcConnectorPackage.exe;driver=..."). Maybe it is as simple as adding a ";Timeout=60000" to set the timeout to 10 minutes.
Also, to get around timeouts, if the SQL statement is taking a long time, perhaps a Materialized View is something you could utilize (at least it would work until you have resolved the parms). Basically Select the data into a MV and have the Qlik Load Script point to it. Of course you'd have to do something to Refresh the MV(s) as needed, an extra step, but has worked for me with timeout issues in PostgreSQL.
huuum, i will check that. also, maybe is just stupid as the SQL server timeout is cnfigured to short for remote query.
it look like there is a settings dedicated to remote query.
the default value look to be 600 (10 minutes)
(Settings on our test server, will check value on Live)
I will also see if something similar is required on QMC connection string.
not very familiar with VM...hope that the Timeout stuff will fix the issue.
Sorry to abuse, since it look you have experience, image the issue fixed, Qlik will load whole year data today in 5minutes, what about tomorrow ?
i mean Qlik will only load "fresh" data between today and tomorrow...or reload everything again and again ?
Many thanks for all you help.
I did a new tests today, it seems that Loading fails after 31sec....i did test 4 times, same result.
Error message is
The following error occurred:
QVX_UNEXPECTED_END_OF_DATA: ERROR [HYT00] [Qlik][ODBC SQL Server Wire Protocol driver]Timeout expired.
I did check on SQL server and the Remote query timeout is set to 600.
i have also tried to add
but still same issue: same query took 54sec on SQL management studio.
Well, I have recently had the same experience with a 30 second timeout for a Postgres DB, so it is a clue that you´re also having the same experience with SQL. Basically, if it takes the DB more than 30 seconds to begin returning rows, something will cause a timeout. Unfortunately I'm having trouble figuring out where/what that setting is.
To make things for work for now, I used Materialized Views (see notes above).
Hopefully someone in the community here can advise.
hic may have it?
Will update if I find the reason/fix.
thank you unfortunately im not familiar at all with MV, i had a quick check on Internet and i understand that the Materialized view is a "stored" view, like for any "normal" table.
In that case that will cause the db size will be multiplicate by 2.
what i did when starting this trial, is that i created a view with all data that need to be loaded into Qlik.
Interesting info, you gave me.
on SQL management studio, when i run a query on my view (for 2 month) the first rows are returned after 54Sec.
but when ru the same query but just for a Month the first rows are returned after 26Sec !
it looks like there is a 30sec barrier !
ok ok we could increase the SQL server ressources in order to make it answer faster, buuut i guess that Qlik could wait longer than 30sec ...
Many thanks to you Stefan for your assistance and time you spend on that.
We finally fixed issue by using QVD
basically the Query is executed as usually on Qlik server but not loaded on Qlik DB
the resulat of the Queries are stored on "flat" file : QVD file
Increadibly faaaaaaast => 1 year of data faster than 1 month with "basic" loading method.
then just have to LOAD the content of the QVD...which is faster again...less than 1min for year
hope it could help anyone else in same situation.
Have anice day.