Capture Script error in the SQL Call statement in Qlikview

[qlik107]

Hi Team,

               I wish to capture the script in the SQL call statement in Qlikview. But I am getting null values in the Error details field. The Sample script is mentioned below.

Sub SQL_HODIM(Date_Begin,Date_End,Type)
SWITCH Type

CASE 1

Let TypeFile = '';

CASE 2

Set TypeFile = _9M;

End Switch;

$(vcLDSSystemType)_DLV:
lOAD
'$(vcLDSSystem)' AS $(vcLDSSystemType)_SYS_SOURCE
, '$(vcLDSInstance)'  as $(vcLDSSystemType)_SYS_INSTANCE
, '$(vcLDSSystemType)' as $(vcLDSSystemType)_SYS_TYPE

, $(EXTRACTIONS_QVD_ADDITIONAL_FIELD_EXP)
,$(vcINCLUDELOGON) as INCLUDELOGON  
, *
;
LOAD
*
;
sql
SELECT
*
FROM
$(EXTRACTIONS_VIEW)
WHERE
"LDS_LOADING_DATE" >= '$(Date_Begin) 00:00:00'
and
"LDS_LOADING_DATE" <= '$(Date_End) 23:59:59'; 
;

// Variable to capture the script error details - By Ashish 16th Jun 2017
let vcScriptErrorDetailsTemp=ScriptErrorDetails;

IF ISNull(NoOfRows('$(vcLDSSystemType)_DLV')) then 
DLV_HODIM_SYSTEM_ERROR_DESCR_TEMP:
LOAD
'$(vcLDSSystem)' AS $(vcLDSSystemType)_SYS_SOURCE,
'$(vcLDSInstance)'  As $(vcLDSSystemType)_SYS_INSTANCE,
'$(vcLDSSystemType)' As $(vcLDSSystemType)_SYS_TYPE,
'$(vcLDSEXTRACTIONS_GROUPS)'  As $(vcLDSSystemType)_SYSTEM,
'$(vcINCLUDELOGON)'  As INCLUDELOGON,
'$(ScriptErrorDetails)'  As $(vcLDSSystemType)_SCRIPT_ERROR_DETAILS,
Date(Today(),'DD/MM/YYYY')  As $(vcLDSSystemType)_SCRIPT_RUN_DATE
Autogenerate 1;
ELSE

Output:

[Anil_Babu_Samineni]

How is your current output looks like?

Can you change CASE statement from SQL to Qlikview

[qlik107]

Hi Anil,

            Is the SQL CASE statement creating the issue. Since I am to get ScriptErrorlist with the same approach. Please suggest some alternate solution.

[Anil_Babu_Samineni]

I am not sure, Does this the issue with CASE. Because, Qlik can write and capture the SQL Functions with in the script. Will you able to help us to understand the rek

SET vcScriptErrorDetailsTemp = 'ScriptErrorDetails'; 

What is the use of this Variable in your case?

[qlik107]

Hi Anil,

           The variable vcScriptErrorDetailsTemp is used to capture the ODBC Script error as mentioned below in the script.

// Variable to capture the script error details - By Ashish 16th Jun 2017
let vcScriptErrorDetailsTemp=ScriptErrorDetails;

IF ISNull(NoOfRows('$(vcLDSSystemType)_DLV')) then 
DLV_HODIM_SYSTEM_ERROR_DESCR_TEMP:
LOAD
'$(vcLDSSystem)' AS $(vcLDSSystemType)_SYS_SOURCE,
'$(vcLDSInstance)'  As $(vcLDSSystemType)_SYS_INSTANCE,
'$(vcLDSSystemType)' As $(vcLDSSystemType)_SYS_TYPE,
'$(vcLDSEXTRACTIONS_GROUPS)'  As $(vcLDSSystemType)_SYSTEM,
'$(vcINCLUDELOGON)'  As INCLUDELOGON,
'$(ScriptErrorDetails)'  As $(vcLDSSystemType)_SCRIPT_ERROR_DETAILS,
Date(Today(),'DD/MM/YYYY')  As $(vcLDSSystemType)_SCRIPT_RUN_DATE
Autogenerate 1;

// log error view

[qlik107]

Error: SQL##f - SqlState: 42000, ErrorCode: 229, ErrorMsg: [Microsoft][ODBC SQL Server Driver][SQL Server]The SELECT permission was denied on the object 'DELIVERY_MONITOR_GET_DELIVERIES', database 'HODIM', schema 'dbo'.

[Anil_Babu_Samineni]

I assume, It could be access issue over the DB.

FYI - The problem may resolved when you adding the NT Authority / Network Service user to the DB users and giving him the appropriate permission in the DB(Make sure that this use has "select" permission on the DB by clicking on the "effective permission").

Final Conclusion - You must check with DB team about Select statement is grant to that specific USER. It may helps to you

[qlik107]

Hi Anil,

            I am aware that it is Permission issue for respective DB Connection. But I wish to capture this error in application.

[Anil_Babu_Samineni]

This is interesting Logs and error codes ‒ QlikView

Meantime, I may check in my environment. Let's see how it went