Error: Unable to connect for result set - NPrinting API

[Adebola]

We are regularly facing an issue with tasks reloads using NPrinting API in Qlikview from the QMC. The tasks fails not at the connection status but at the task status as below:

CUSTOM CONNECT TO ***
2020-06-09 06:04:21 Connected.
2020-06-09 06:04:21 1521 RestNPExecutionsTable:
2020-06-09 06:04:21 1522 SQL SELECT
2020-06-09 06:04:21 1523 "id",
2020-06-09 06:04:21 1524 "result",
2020-06-09 06:04:21 1525 "progress",
2020-06-09 06:04:21 1526 "status",
2020-06-09 06:04:21 1527 "completed"
2020-06-09 06:04:21 1528 FROM
2020-06-09 06:04:21 1529 JSON (wrap off) "data"
2020-06-09 06:04:21 1530 WITH
2020-06-09 06:04:21 1531 CONNECTION( URL "https://cln-kview05.sbet-emea.ads:4993/api/v1/tasks/d902062a-5280-453a-8179-d5b2b1e6aeb7/executions/...", HTTPHEADER "cookie" "NPWEBCONSOLE_SESSION=d08f80b1f4f767914b00743699e9740cc26035db-%00NPWEBCONSOLE_XSRF-TOKEN%3AI4rC46h2G%2FB4i5oOHot%2FWX9%2F%2F7rLB%2BwxcjUYyPtT%2BDY%3D%00%00_TS%3Asession%00%00userid%3A21c5c3a055824ca99e858ddff1f802a2%00%00userlogin%3A2020-06-09T05%3A34%3A04.1717566Z%00; ", QUERY "Limit" "2000")
2020-06-09 06:04:22 Error: Unable to connect for result set
2020-06-09 06:04:22 Unable to connect for result set
2020-06-09 06:04:22 Execution Failed
2020-06-09 06:04:22 Execution finished.

How can we resolve this issue? Thank you

[Lech_Miszkiewicz · Accepted solution]

Hi,

How often do you experience this? If it is something you can reproduce easily I suggest you open Qlik Support case. I once tried to capture logs etc.. but because in my environment issue was not persistent I gave up. 

In my scripts I use "sleep" script function to wait few second before i check status each time, which then limits number of API calls and possibility of overload. I also use in my scripts failover solution which retries to establish connection if there is an error. You may want to implement something similar in your script.

Link to my scripts is on my blog (search for failover in NPrinitng.qvs file to find a sample of failover). Failover is only applied to connection status at the moment, but you can modify your script to use practically the same logic when trying to retrieve task status.

https://nprintingadventures.com/2019/04/08/nprinting-api-qlik-rest-subroutines/

The actual problem may be sitting more on Rest connector side than NPrinting side, but to determine that we will need support help.

                        //  Check Metadata reload status
                    
                                LIB CONNECT TO '$(vConnection_GET)';
                    
                        //  Reset Connection Status
                        
                                Let vConnectionStatus = '';

                        //  Loop until 
                        //      * Status "Generated" 
                        //      * Exit if status "Failed"
                        //      * Exit if status "Enqueued","Generating" for longer than X Minutes  
                        //      * If there is a problem with retriving "status" from endpoint re-try (failover)

                            IF '$(vNP_ReloadDuration)'='' THEN
                                Let vNP_ReloadDuration      =   30;
                            ENDIF    
                            Let vNP_ReloadDuration      =   $(vNP_ReloadDuration)/1440;               //  This gives interval in minutes
                            Let vNP_ReloadDurationFmt   =   Interval($(vNP_ReloadDuration),'mm:ss');
                            Trace Metadata reload will be stopped after $(vNP_ReloadDurationFmt) minutes;

                            DO while (vConnectionStatus <> 'Generated')
                            
                                Trace   ---;
                                Trace   Metadata Reload for Connection "$(vNPrintingConnectionName)" "$(vConnectionId)";
                                Trace   ---;
                                Trace   Metadata reload status: "$(vConnectionStatus)";
                                Sleep 5000;
                                
                                // If there is an issue with metadata reload status retrival (error) re-try 5 times then end with ERROR!
                                
                                // Suppress reload failure in case of connection failure
                                    
                                    Set ErrorMode       = 0;
                                        
                                    RestConnectionStatusTable:
                                    SQL SELECT
                                        "cacheStatus"
                                    FROM 
                                        JSON (wrap off) "data"
                                    WITH 
                                        CONNECTION( URL "$(vConnectionStatusURL)", HTTPHEADER "cookie" "$(vCookie)", QUERY "Limit" "$(vQueryLimit)")
                                    ;  

                                    // check if Error and re-try connection
                                    
                                    Let vErrorMessage = ScriptError;
                                    If Len(vErrorMessage) > 0 Then
                                        
                                        Let vErrorCount 		= 1;
                                        Let vErrorCountLoop		= 6;
                                        
                                        Trace Connection error No $(vErrorCount), Error: '$(vErrorMessage)';
                                        // Create log
                                        Log:
                                        Load
                                            now()                                   as time,
                                            'Connection error No $(vErrorCount)'    as Error
                                        Autogenerate
                                            (1)
                                        ;
                                        Store Log into $(vConnection)\Log\NPrinting_Log.txt (txt, delimiter is '|' );
                                        Drop Table Log;

                                        // Enter Loop until success (but max 5 times) to try get "cacheStatus"
                                        DO while (vErrorCountLoop > 0)
                                            
                                            LIB CONNECT TO '$(vConnection_GET)';

                                            RestConnectionStatusTable:
                                            SQL SELECT
                                                "cacheStatus"
                                            FROM 
                                                JSON (wrap off) "data"
                                            WITH 
                                                CONNECTION( URL "$(vConnectionStatusURL)", HTTPHEADER "cookie" "$(vCookie)", QUERY "Limit" "$(vQueryLimit)")
                                            ;
                                            
                                            Let vErrorMessage = ScriptError;
                                            IF Len(vErrorMessage) > 0 Then
                                                Let vErrorCount 		= $(vErrorCount)+1;
                                                Let vErrorCountLoop 	= 5-$(vErrorCount);
                                                Trace Connection error No $(vErrorCount), loop No $(vErrorCountLoop), Error: '$(vErrorMessage)';
                                                
                                                // Create log
                                                Log:
                                                Load
                                                    now()                                   as time,
                                                    'Connection error No $(vErrorCount)'    as Error
                                                Autogenerate
                                                    (1)
                                                ;
                                                Store Log into $(vConnection)\Log\NPrinting_Log.txt (txt, delimiter is '|' );
                                                Drop Table Log;


                                            ELSE
                                                Trace No error - resume script execution!;
                                                Let vErrorCountLoop 	= 0;
                                            ENDIF

                                        Loop    

                                    ENDIF
                                    
                                    Set ErrorMode       = 1;

                                    [connection_data]:
                                    LOAD 
                                        [cacheStatus] AS [connection_cacheStatus]
                                    RESIDENT 
                                        RestConnectionStatusTable
                                    ;
                    
                                    Let vConnectionStatus = Peek('connection_cacheStatus',0,'connection_data');
                    
                                    DROP TABLE RestConnectionStatusTable;
                                    DROP TABLE [connection_data];

                                //  SAFETY PLUG - If reload goes for too long EXIT and ABORT  

                                Let vMetadataReloadTime     =   num(Now()) - $(vNow);
                                Let vMetadataReloadTimeFmt  =   Interval($(vMetadataReloadTime),'mm:ss');
                                Trace Reloading Metadata for $(vMetadataReloadTimeFmt);

                                IF $(vMetadataReloadTime) > $(vNP_ReloadDuration) THEN
                                    Trace   ***;
                                    Trace   ****    ****    ****    ****    ****;
                                    Trace   *** Aborting Metadata Reload    ***;
                                    Trace   *** Subsequent tasks aborted    *** ;
                                    Trace   ****    ****    ****    ****    ****;
                                    Call NP_ConnectionReloadAbort('$(vConnectionId)')
                                    Trace   ****    ****    ****    ****    ****;
                                    Trace   Aborted!!!;
                                    Trace   ****    ****    ****    ****    ****;
                                    Exit Script;
                                ENDIF

                            Loop
                            
                            Trace   ---;
                            Trace   Metadata reload status: "$(vConnectionStatus)";

[Adebola]

Hello Lech,

Unfortunately, we cannot re-produce this error at will, although it is beginning to happen frequently - we are using  5 different QlikView applications that have been created to use NPrinting APIs to refresh reports after data reload in the QMC, and the failure can occur in any one of them, sometimes, it does not happen at all, all tasks run and reports are generated.

Thank you for your response. I will try what you suggest.

Adebola