Error handling in script

[#1]

The QlikView reference file refers to the "four special variables which can be used for error handling in the script", but does not have an example of how to use all of them - I am hoping someone has an example of a script using ScriptErrorDetails as I can't work out how this one works.  I would like to be able to trap an error message returned from a SQL Server stored procedure, as this would be more useful than just using a general error code.

Thanks

Brian

[#2]

You use 'ErrorMode' to override the usual action of stopping the script with an error dialogue when an error is encountered. This means that you can then test the result of an individual statement with the value of 'ScriptError' e.g 8 = field not found. 'ScriptErrorCount' will return the number of errors during the script execution and 'ScriptErrorList' the details of the individual errors.

I use these when running in 'batch mode' so that a failing script still completes and doesnt lock up my server. I set an alert that is conditioned on 'ScriptErrorCount <>0' and include 'ScriptErrorList' in the email message body.

You can also manipulate 'ScriptErrorCount' and 'ScriptErrorList' within the script to add critical errors of your own.

I think the on-line help should give you better examples.

Regards,

Gordon

[#3]

Gordon

Thanks for your reply - but can I give some more detail of what I am trying to do and how I am trying to achieve it.

To test what I am doing I have set up a stored procedure that raises an error - if I run it in SQL Server Management Studio this is the result I see:

I have created a script in QlikView to call this stored procedure, and I'd like to have access to the message 'This is an error returned from the procedure', and hopefully to the return code of the procedure as well (-1 in this case).  My QV script looks like this:

I was expecting the message might be in my variable vScriptErrorDetails, but that is getting nothing at all:

The Reference Manual and the online help describes the error variables, but gives no examples of usage of ScriptErrorDetails or ScriptErrorList.  There is also mention of ScriptErrorDetailed, but not sure if this is a typo or another variable.

Any ideas?

[#4]

Brian,

But doesnt the last image ('Variable Overview') show that you are getting an error returned from the stored procedure? I think that is all you will get from Qlikview because the scripterror variables are really designed for just that - identifying errors in the script and not from an external process.

I think the manuals reference to 'ScriptErrorDetailed' is a typo.

Regards,

Gordon

btw You don't need to assign the script error variables to your own in the script

[#5]

Here is my example which I finally got working.  Not sure if it is the most efficient manner.  My goal below is to NOT stop the tool data refresh when it comes to logic related to storing my data quality statistics and metrics.  I want to just insert blindly, BUT record the errors so I can go back and review issues to fix them later.  I highlighted the key code in bold.

Couple of tricks...

1. The use of the error variables MUST occur immediately after the command in question that you want to monitor/detect.  It is not like Java catch exception function where is jumps down to the code that handles any error.  As soon as the next command is run the several of the error variables are reset... specifically ScriptError and ScriptErrorDetails.  It is ScriptErrorDetails that contains the rich ODBC SQL error output information.
   1. ScriptErrorCount and ScriptErrorList maintain their state and are incrementally updated with each error.
   2. What I did was INSERT all the error variables into an TEMP1 table regardless of if there was an error or not, then go back and ONLY keep the error records by inserting them into an ERROR table where the ScriptError (ERROR_ID) number is not zero which indicates an error.  I supposed I could have made intermediate storage variables but I kept getting a null value in the ScriptErrorDetails field,
2. Script error is BOTH a number and a text variable which is kinda cool.
   1. '$(#ScriptError)' as ERROR_ID,
   2. '$(ScriptError)' as ERROR_NAME,

3. The error fields are not associated with a table so for the INSERT to my temp tabld to work I could use any other table, in this case STATISTIC.  Since it would insert the static data each time for every record in the STATISTIC table, I put "First 1" to limit the insert.

~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~

trace('Stats - Save out to database');

ODBC CONNECT TO [Central Data];

//if stats table is empty do nothing

if noOfRows('STATISTIC') <> Null() then

          //get total number of row for loop

          let noRows = NoOfRows('STATISTIC');

          //turn of reporting exceptions to standard output (silent mode).

    set ErrorMode=0;

          //0 denotes the first record, 1 the second record and so on

          for i=0 to $(noRows) // loop through every row

                    //get out my stats values to save to the dataabse.

                    let vSTAT_TOOL   = Left(peek('STAT_TOOL',   $(i), 'STATISTIC'),50);

                    let vSTAT_TABLE  = Left(peek('STAT_TABLE',  $(i), 'STATISTIC'),50);

                    let vSTAT_TYPE   = Left(peek('STAT_TYPE',   $(i), 'STATISTIC'),50);

                    let vSTAT_VALUE  = Left(peek('STAT_VALUE',  $(i), 'STATISTIC'),200);

                     //TO FIX:  bug in one of my stats generators which is inserting empty row into stats table which cause an eror.

                    if '$(G_ApplicationName)'<>'$(vSTAT_TOOL)' then

                              trace('Stats record was null or value empty or tool does not match.  No insert.');

                    else

                              //insert stats to database.

                              SQL INSERT INTO [CD_DEV].[dbo].[STATISTIC]

                                        (   [STAT_TOOL]     ,[STAT_TABLE]      ,[STAT_TYPE]     ,[STAT_VALUE] )

                              VALUES ('$(vSTAT_TOOL)' ,'$(vSTAT_TABLE)', '$(vSTAT_TYPE)', '$(vSTAT_VALUE)' );

                              //I use STATISTIC table as a proxy to make the insert work, but since my values are static

                              //and not related to STATISTIC table I do not want to repeat the statis values for each record

                              //in the STATISTIC table, so I use "First 1" to just insert once to the TEMP1 table.

                              //I load all insert status metadata to this temp1 table and will delete the non error records after

                              TEMP1:

                              First 1 LOAD

                                        '$(ScriptErrorCount)' as ERROR_SEQ_NUMBER,

                                        '$(#ScriptError)' as ERROR_ID,

                                        '$(ScriptError)' as ERROR_NAME,

                                        '$(ScriptErrorDetails)' as ERROR_DETAIL,

                                        '$(vTodayFormat)' as ERROR_DATE

                              Resident STATISTIC;

                    end if //end the test if application name is empty

          //get the next STATISTIC reocrd

          next

          set ErrorMode=1;

//there is some bug I could not figure out where the "ERROR:" table naming was being ignored

   //which resulted in me reinserting the same records into the TEMP1 table.

// Using quality appends the table name to the columns naming routine, and forced the hand in setting the table name.

          QUALIFY *;

          //load only really errors to ERROR table.

          ERROR:

          Load

                    *

          Resident TEMP1

          Where

                    ERROR_ID <> 0;

          UNQUALIFY *;

//clean up

          DROP Table TEMP1;

          //Now that permanent stored to datbase, I can get rid of the resident table.  I pull all the

          //statistics for ALL days on the next scrtab to show time series of key statistics and metrics.

          DROP Table STATISTIC;

end if

[#6]

Thanks, works perfect!

[mjr6662000]

hi

1. create table in sql server to log error

CREATE TABLE [dbo].[ScriptLoadError](

    [ErrorId] [int] IDENTITY(1,1) NOT NULL,

    [ErrorDesc] [nvarchar](250) NULL,

    [ErrorDateTime] [datetime] NULL,

CONSTRAINT [PK_ScriptLoadError] PRIMARY KEY CLUSTERED

(

    [ErrorId] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

2. create sql stored procedure (sp_ScriptLoadError_Insert) to insert to table

CREATE procedure [dbo].[sp_ScriptLoadError_Insert] @ErrorDesc    as nvarchar(250)

as

INSERT INTO [dbo].[ScriptLoadError]

           ([ErrorDesc]

           ,[ErrorDateTime])

     VALUES(@ErrorDesc, getdate())

3.copy this in qlikview edit script after run query script

//*********************Error Handeling*************************************/

set vScriptErrorDetails = $(ScriptErrorDetails);

if ScriptErrorCount > 0 then

OLEDB CONNECT TO [Provider=SQLOLEDB......;

sql sp_ScriptLoadError_Insert N'$(vScriptErrorDetails)';

exit script;

end if

//*********************Error Handeling*************************************/

for more

Error Handling in Qlikview