Error: fetched column value was truncated

I got an error yesterday on a QlikView document that had reloaded dozens of times before. It appeared to be an Oracle SQL error. The error message that appeared in the pop-up box during loading said, SQL Error:[Oracle][ODBC][Ora]ORA-01406:fetched column value was truncated

Despite appearing like a SQL error, the piece of SQL code didn’t cause any errors when submitted through Oracle SQL*Plus. It only caused an error when used to load a table in the QlikView document. I was using QlikView version 8.50.6206.5.

With a little trial-and-error I narrowed down the source of the problem to a single database column, named QTY, which was defined in the Oracle database as a FLOAT. Since this document had reloaded successfully in the past, it must be the new data stored in the table that is causing the problem now.

In any case, I was able to make the document load and satisfy the reporting requirements by simply changing the SQL a little from SQL SELECT QTY… to SQL SELECT ROUND(QTY,2)… After this change, the document reloaded with no problem.

I’m hoping this blog entry might help someone fix the problem if it happens to them. And, inasmuch as this blog also serves as my own QlikView notes it might serve as a reminder to myself when it happens again in one of my documents.

(Note the comment describing another experience with this error)

Checking for Loadscript Errors from the Macro code

If you are using the macro code Reload method to load data into your QlikView report you can also easily check for any loading errors. Here's an example of how that could be done. Add a small subroutine to the macro code that can check the system variable for error count like this:

Sub Check_for_errors()
'---------------------
'See if any load script errors occurred and report them
alarm_flag = 0
Set objvar = ActiveDocument.Variables("ScriptErrorCount")
If trim(objvar.GetContent.String) = "0" then
  'No error - hooray
  Exit Sub
  End If
alarm_flag = 1
'Here you can add any other error notification
End Sub

And then, in the subroutine that reloads the document code add an IF statement after the .Reload method something like this:

ActiveDocument.Reload
Check_for_errors 'call Check_for_errors subroutine
If alarm_flag = 1 then 'an error happened in the loadscript
  ActiveDocument.CloseDoc
  ActiveDocument.GetApplication.Quit
  '***Exit Function or Exit Sub
  End If

In the reports I've developed there is error checking every step of the way and any error causes a notification file to be written out. If you have an email client program on the computer where the report is reloading then consider using an email notification to the report users or to a technical support person. I've sometimes sent a small notification email message to my cell phone (My favorite message is "Lassie, go for help!") so that I can be notified immediately with the bad news.

Checking for errors when a report reloads automatically

In a recent posting (Click here to read it) we discussed ways to run QlikView from the command line as part of an automated script process. If you run the reload process automatically then you're faced with an issue: what should happen if an error occurs during the reload? Even a report that has reloaded successfully dozens of times before can run into an error if a file is missing or the database is down or if someone changed the title on a spreadsheet column (that's the one I seem to trip over most often).
If you're going to sit at your desk in front of the computer and watch it run then you don't have to do anything -- any loadscript error window that opens up can be clicked and the report can be cancelled if necessary. But, if the report is going to load unattended without anyone watching then consider these actions:

Check the Generate Logfile box on the General tab of the Document Properties window (select Settings->Document Properties from the menu - see the picture above). This is often a good idea whether you are reloading the report automatically or not. It causes QlikView to generate a text file audit trail of what happens as the loadscript runs. It captures the number of rows returned from queries and which branch an IF statement takes, it shows the expanded value of document variables that are used in commands and expressions and it shows the date and time for each command which allows you to analyze the slow-running parts of the loadscript. The logfile will be named just like the report file but with a ".log" added to the end. For example, if your report file is named Prior_Yr_Sales.qvw then the log file will be named Prior_Yr_Sales.qvw.log and it will be stored in the same folder as the report file. Open the log file with Windows Notepad or Wordpad. You can search for errors in the log file by using the Notepad or Wordpad Search function and search for Error:

For a report that must reload unattended (with no human folk watching) add this line to the top of your loadscript:
  SET ErrorMode=0;
That will tell QlikView not to open a loadscript error window when an error occurs (which would wait for someone to click OK). Instead the loadscript will go through all of the commands and do the best it can to execute all of them.

From your .bat or .cmd script you can have the script search for any lines containing the text Error: and perform an action if it is found. Here's an example:
rem Initialize the log file with xxx
echo xxx > c:\RPTS\Inventory.qvw.log
rem Now, run QlikView and reload
"c:\Program\QV.exe" /r /vbatch_flag=1 c:\RPTS\Inventory.qvw
rem Look through the log file and write
rem a note to Joe if we find an error
Type c:\RPTS\Inventory.qvw.log Find /c "Error:"
If errorlevel 1 echo %date% %time% "Error occurred" >> c:\Notify_Joe.txt