8 Replies Latest reply: Dec 1, 2016 12:03 PM by Stefan Stefansson RSS

    SQL time out message during LOAD process (sense 3.1 SR1)

    mustapha Laouari

      Hi there !

       

      We are trying the Qlik sense server.

      We have a Microsoft SQL server DB, where they are many rows (about 3 Millions / year).

      I did the best to remove all not necessary data, unfortunately, it seems that Qlik sense refusing to Load data.

       

      i have reduced to 1 year (VS 5 years) => same pb

      3 months (about 800000 rows) => same pb

      1 month (about 300000 rows) => OK

       

      I have tried to load (display) data of a year on SQL management studio ... of course it took a while but it works.

       

      could you help me please.

      i do not see anywhere if there is any data size limitation with Qlik sense.

      Also, in our project we expected to see "Live" data on Qlik , i mean we expected to make a scheduled task in order to reload data every 15 min.

      (by the way, does Qlik reloads All data evrytime, or just "new" data ?)

       

      MANY MANY thanks for all help you could provide.

      i guess you understood that English is not ma native language, sorry for that ;-)

        • Re: SQL time out message during LOAD process (sense 3.1 SR1)
          Stefan Stefansson

          Can you tell after how long you get the timeout?

            • Re: SQL time out message during LOAD process (sense 3.1 SR1)
              mustapha Laouari

              Hello,

              first, thank you for your interest.

              I would say less than 2 or 3 minutes (i could retry tomorrow if that need to be more accurate...no more server access today)

                • Re: SQL time out message during LOAD process (sense 3.1 SR1)
                  Stefan Stefansson

                  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.

                    • Re: SQL time out message during LOAD process (sense 3.1 SR1)
                      mustapha Laouari

                      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)

                       

                      remotequeryTO.PNG

                       

                      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.

                • Re: SQL time out message during LOAD process (sense 3.1 SR1)
                  mustapha Laouari

                  Hi,

                   

                  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

                  'timeout=60'

                  'ConnectionTimeout=60'

                  'SQL_ATTR_LOGIN_TIMEOUT=60'

                   

                  but still same issue: same query took 54sec on SQL management studio.

                   

                  Many thanks

                    • Re: SQL time out message during LOAD process (sense 3.1 SR1)
                      Stefan Stefansson

                      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.