Script execution reaches end, but doesn't conclude, and no error messages

[stevelord]

Hi, I've pressed reload on a dashboard, and it spent a few minutes running through the script.  The Script Execution Progress window shows it has reached what I know is the last table and appears to have completely loaded it, but the dialogue window is not closing itself even though I have that checkbox selected.  It is also not letting me close it with the end here button, and I've had to close qlikview with task manager a couple times now.

Can someone provide me possible scenarios and solutions for this phenomenon?  The dashboard is on qlikview server and is live, I was just sneaking in a couple more fields and reloading it.  There are no errors in the script that I can see, and the Task Manager shows CPU Usage at ~25% and Physical Memory hanging at 80%.  (I've pushed the first past 80% and the second to 99% without crashing anything, so pretty sure those aren't the issue.)

[Karim_Khan]

Hi Steve,

PFB Error logs Data and script

Logs:

12/21/2015 8:26:48 PM:      Execution started.

12/21/2015 8:26:48 PM:      QlikView Version:11.00.11154.0

12/21/2015 8:26:48 PM:      CPU Target                    x64

12/21/2015 8:26:48 PM:      Operating System              Windows 7 Professional Service Pack 1 (64 bit edition)

12/21/2015 8:26:48 PM:      Wow64 mode                    Not using Wow64

12/21/2015 8:26:48 PM:      MDAC Version                  6.1.7601.17514

12/21/2015 8:26:48 PM:      MDAC Full Install Version     6.1.7601.17514

12/21/2015 8:26:48 PM:      PreferredCompression          2

12/21/2015 8:26:48 PM:      EnableParallelReload          1

12/21/2015 8:26:48 PM:      ParallelizeQvdLoads           1

12/21/2015 8:26:48 PM:      AutoSaveAfterReload           0

12/21/2015 8:26:48 PM:      BackupBeforeReload            1

12/21/2015 8:26:48 PM:      EnableFlushLog                0

12/21/2015 8:26:48 PM:      SaveInfoWhenSavingFile        0

12/21/2015 8:26:48 PM:      UserLogfileCharset            0

12/21/2015 8:26:48 PM:      OdbcLoginTimeout              -1

12/21/2015 8:26:48 PM:      OdbcConnectionTimeout         -1

12/21/2015 8:26:48 PM:      ScriptWantsDbWrite            false

12/21/2015 8:26:48 PM:      ScriptWantsExe                false

12/21/2015 8:26:48 PM:      LogFile CodePage Used:        1252

12/21/2015 8:26:56 PM: 0002  SET ThousandSep=','

12/21/2015 8:26:56 PM: 0003  SET DecimalSep='.'

12/21/2015 8:26:56 PM: 0004  SET MoneyThousandSep=','

12/21/2015 8:26:56 PM: 0005  SET MoneyDecimalSep='.'

12/21/2015 8:26:56 PM: 0006  SET MoneyFormat='$#,##0.00;($#,##0.00)'

12/21/2015 8:26:56 PM: 0007  SET TimeFormat='h:mm:ss TT'

12/21/2015 8:26:56 PM: 0008  SET DateFormat='M/D/YYYY'

12/21/2015 8:26:56 PM: 0009  SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'

12/21/2015 8:26:56 PM: 0010  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

12/21/2015 8:26:56 PM: 0011  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

12/21/2015 8:26:56 PM: 0017  CUSTOM CONNECT*Provider*JDBCConnector_x64.dll*XUserId*XPassword*

12/21/2015 8:27:01 PM: 0018  Directory

12/21/2015 8:27:01 PM: 0019  T_WAR_PRODUCT:

12/21/2015 8:27:01 PM: 0020 

12/21/2015 8:27:01 PM: 0021 

12/21/2015 8:27:01 PM: 0022 

12/21/2015 8:27:01 PM: 0023  SQL SELECT *

12/21/2015 8:27:01 PM: 0024  FROM ucpprd.ucp."t_war_product"

12/21/2015 8:27:03 PM:       16 fields found: visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt, 100 lines fetched

Script code:

CUSTOM CONNECT TO "Provider=JDBCConnector_x64.dll;jdbc:redshift://ucpprd.cusjr55gdzdb.us-east-1.redshift.amazonaws.com:5439/ucpprd?ssl=true&sslfactory=com.amazon.redshift.ssl.NonValidatingFactory;XUserId=CTCZMadLULaOXQVNNF;XPassword=RBfVGadLULaOXQVNDJQIWZNJRRMGDUC;";

Directory;

T_WAR_PRODUCT:

//First 1000000

//LOAD *;

SQL SELECT *

FROM ucpprd.ucp."t_war_product";

STORE T_WAR_PRODUCT INTO D:\UCP\KK_WAR_PRODUCT.qvd (qvd);

[Karim_Khan]

Hi Steve,

   I have noticed the error if i am fetching 10 columns then in the result i am getting 10 columns data and extra comma is coming .don't know why its coming and how to resolve it?

Error_Logs:

12/21/2015 8:46:14 PM: 0023  SQL SELECT visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt

12/21/2015 8:46:14 PM: 0024  FROM ucp.t_war_product

12/21/2015 8:46:16 PM:       16 fields found: visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt, 10 lines fetched

Regards,

KK

[stevelord]

Hi Karim, I think you need to remove the // from in front of your LOAD *; or you're telling Qlikview to select things but not load anything.  Like so:

//First 1000000

LOAD *;

SQL SELECT *

FROM ucpprd.ucp."t_war_product";

After that, the //First 1000000 can be left out probably (with the // in front of it it's not doing anything now though).  I see it only fetched 100 rows from the select statement, so not sure what would happen if you tried to tell it to load the first million and only a hundred were present.

Lastly, I have noticed in my own data model that if I try to load 'the first 10' and those rows happen to be null values in the fields I selected, it will load nothing rather than skip to the rows that have values.  (Commonly happens if the first bunch of records are just test records and test user didn't populate all fields.  First 1000 or first 10000 tends to get me past those test people in our data set.)

Edit: You will also want to edit your post to truncate or scramble the xuserid and xpassword stuff.  The xpassword is encrypted by Qlikview, but people with advanced knowledge of know how that works might know how to decrypt it.  (I always just go with abc123 as a placeholder in forum posts and just woe to anyone who actually uses abc123 for a password. )

[Karim_Khan]

Then why it is not working

FIRST 10

SQL SELECT *

from ucpprd.ucp."t_war_product";

I have noticed the error if am fetching 16 columns from my table then in the result its giving me the 16 columns plus extra comma(,)is coming due to which it is going into infinite loop now am investigating that only how to sort this.

Regards,

KK

[stevelord]

Hi Karim, instead of SQL Select *; you could list the fields and see if that solves the issue.  Infinite loop sounds like a good culprit for something never finishing though.   I do believe you need to keep a Load *; above your SQL Select if you want qlikview to actually hold any of the data for you to use on the front end too.

Load *;

SQL Select

field1,

field2,

field3

FROM table;

[Karim_Khan]

Hi Steve,

    I have tried your suggestion also now some new log data is genrated but script execution is not getting finished.

12/21/2015 10:45:06 PM:      Execution started.

12/21/2015 10:45:06 PM:      QlikView Version:11.00.11154.0

12/21/2015 10:45:06 PM:      CPU Target                    x64

12/21/2015 10:45:06 PM:      Operating System              Windows 7 Professional Service Pack 1 (64 bit edition)

12/21/2015 10:45:06 PM:      Wow64 mode                    Not using Wow64

12/21/2015 10:45:06 PM:      MDAC Version                  6.1.7601.17514

12/21/2015 10:45:06 PM:      MDAC Full Install Version     6.1.7601.17514

12/21/2015 10:45:06 PM:      PreferredCompression          2

12/21/2015 10:45:06 PM:      EnableParallelReload          1

12/21/2015 10:45:06 PM:      ParallelizeQvdLoads           1

12/21/2015 10:45:06 PM:      AutoSaveAfterReload           0

12/21/2015 10:45:06 PM:      BackupBeforeReload            1

12/21/2015 10:45:06 PM:      EnableFlushLog                0

12/21/2015 10:45:06 PM:      SaveInfoWhenSavingFile        0

12/21/2015 10:45:06 PM:      UserLogfileCharset            0

12/21/2015 10:45:06 PM:      OdbcLoginTimeout              -1

12/21/2015 10:45:06 PM:      OdbcConnectionTimeout         -1

12/21/2015 10:45:06 PM:      ScriptWantsDbWrite            false

12/21/2015 10:45:06 PM:      ScriptWantsExe                false

12/21/2015 10:45:06 PM:      LogFile CodePage Used:        1252

12/21/2015 10:45:06 PM: 0002  SET ThousandSep=','

12/21/2015 10:45:06 PM: 0003  SET DecimalSep='.'

12/21/2015 10:45:06 PM: 0004  SET MoneyThousandSep=','

12/21/2015 10:45:06 PM: 0005  SET MoneyDecimalSep='.'

12/21/2015 10:45:06 PM: 0006  SET MoneyFormat='$#,##0.00;($#,##0.00)'

12/21/2015 10:45:06 PM: 0007  SET TimeFormat='h:mm:ss TT'

12/21/2015 10:45:06 PM: 0008  SET DateFormat='M/D/YYYY'

12/21/2015 10:45:06 PM: 0009  SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'

12/21/2015 10:45:06 PM: 0010  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'

12/21/2015 10:45:06 PM: 0011  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'

12/21/2015 10:45:06 PM: 0012  SET ErrorMode=0

12/21/2015 10:45:06 PM: 0014  CUSTOM CONNECT*Provider*JDBCConnector_x64.dll*XUserId*XPassword*

12/21/2015 10:45:11 PM: 0016  FIRST 10

12/21/2015 10:45:11 PM: 0017  LOAD *

12/21/2015 10:45:11 PM: 0018  SQL SELECT visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt

12/21/2015 10:45:11 PM: 0019  from ucpprd.ucp."t_war_product"

12/21/2015 10:45:12 PM:       16 fields found: visit_dt, visitor_id, visit_nbr, ip_addr, hit_seq_nbr, hit_ts, site_nm, site_section_nm, product_nm, page_nm, product_view_cnt, product_edu_view_cnt, trial_initiate_cnt, cloud_initiate_cnt, edu_download_start_cnt, dt, 10 lines fetched

12/21/2015 10:45:13 PM: 0023  STORE KK_PRODUCT INTO D:\UCP\KK_UAT.QVD(QVD)

Regards,

KK

[Karim_Khan]