topic Re: Capture Script error in the SQL Call statement in Qlikview in QlikView

Capture Script error in the SQL Call statement in Qlikview

qlik107 — Sun, 18 Jun 2017 16:47:13 GMT

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:

Re: Capture Script error in the SQL Call statement in Qlikview

Anil_Babu_Samineni — Mon, 19 Jun 2017 02:07:46 GMT

How is your current output looks like?

Can you change CASE statement from SQL to Qlikview

Re: Capture Script error in the SQL Call statement in Qlikview

qlik107 — Mon, 19 Jun 2017 05:13:40 GMT

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.

Re: Capture Script error in the SQL Call statement in Qlikview

Anil_Babu_Samineni — Mon, 19 Jun 2017 05:17:23 GMT

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?

Re: Capture Script error in the SQL Call statement in Qlikview

qlik107 — Mon, 19 Jun 2017 06:29:50 GMT

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

Re: Capture Script error in the SQL Call statement in Qlikview

qlik107 — Mon, 19 Jun 2017 06:31:20 GMT

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'.

Re: Capture Script error in the SQL Call statement in Qlikview

Anil_Babu_Samineni — Mon, 19 Jun 2017 06:39:21 GMT

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

Re: Capture Script error in the SQL Call statement in Qlikview

qlik107 — Mon, 19 Jun 2017 06:45:47 GMT

Hi Anil,

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

Re: Capture Script error in the SQL Call statement in Qlikview

Anil_Babu_Samineni — Mon, 19 Jun 2017 07:37:36 GMT

This is interesting Logs and error codes ‒ QlikView

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