2 Replies Latest reply: Jul 12, 2016 9:11 AM by Greg Donnells RSS

    Load script error after 10 minutes

    Nasja van Meerten

      Hi everyone,

       

      When I use the dataloadeditor to load data by sql query (odbc) I sometimes make mistakes in my script syntax .

      But the problem is that Qlik waits 10 minutes to show me that error when I run the script without debug-mode.

      It doesn't seem to be doing anything. I have to wait for about 10 minutes and 30 seconds before I get a simple message like "connection not found", or "can't find field XXXX". Seems like something it would know after 1 second. Is this a parameter somewhere, that Qlik keeps trying for x minutes before returning an error?

       

      Thank you, Nas

        • Re: Load script error after 10 minutes
          Gysbert Wassenaar

          There is no timeout setting in Qlikview. Perhaps the odbc driver has one. You'll have to look at the documentation of the odbc driver to find that out. That's not part of Qlikview.


          You can also use the Debug mode and then specify how many records you want to load in the debug run. Or you can add a line like FIRST 100 above the sql load statement. I recommend debugging properly to make sure all the load statements are correct and return results.

          • Re: Load script error after 10 minutes
            Greg Donnells

             

            There may be several issues in your environment – please review the behaviors below and fix any that may apply:

             

             

             

            1. There is an inherent timeout in most ODBC drivers at 10 minutes for the command object such that when you start a query, if it does not return data in 10 minutes, the query will timeout and generate a TDS protocol error (or some other nebulous inappropriate error message), resulting in a QV script error and the infamous cannot read the ScriptErrorCount variable message. ** The solution is to specify a timeout parameter on the ODBC connection string (this is driver version dependent) and QV has no mechanism to specify this parameter. So, you should move to a custom connection or use the OLE DB provider in QV, where this issue can be fixed by the longer timeout value default (15 minutes) or by specifying an appropriate timeout parameter in the connection string. Note that a custom connection will also permit the use of vendor supplied drivers and additional parameters or options, such as a 5 minute keepalive.

             

             

             

            1. Most network firewalls will timeout inactive connections after a set timeframe. Most firewalls default to 30 minutes, but most network security folks set this parameter to 15 minutes, causing any inactive DB connections to experience the dreaded FinWait state on their socket connections (ie, communications between app server and DB server were terminated unexpectedly). This appears to QV as a nebulous protocol error and looks almost the same as the above error stream. To fix this, have the DBA team enable keepalive packets every 5 minutes on DB connections in the driver setup (might need it both on the DB server and app server to ensure it happens). Some vendor supplied DB drivers (like Oracle) allow the initiating connection to specify this parameter as well.