1 Reply Latest reply: Apr 30, 2012 6:24 PM by Marc Livingston RSS

    SQL 2005 with Incremental QVD Load not working.

      I am trying to set up Incremental Loading with SQL 2005. It keeps on failing when comparing the exec time with the changedate field in the table. Format of field in the table is:

       

      2/2/2010 12:00:00 AM

       

       

       

      Current Script:

       

      Order1:
      
      SQL SELECT 
          company,
          custnum,
          orderdate,
          shpconnum,
          salesreplist,
          openorder,
          orderamt,
          changedate,
          ponum,
          shiptonum,
          ordernum,
          requestdate
      FROM epicor904.dbo.orderhed
      where voidorder='0' and  (changedate <= $(LastExecTime)
      AND changedate < $(ThisExecTime)
      )
      ;
      
      
      Concatenate LOAD company,
          custnum,
          orderdate,
          shpconnum,
          salesreplist,
          openorder,
          orderamt,
          changedate,
          ponum,
          shiptonum,
          ordernum,
          requestdate FROM ....
      WHERE NOT EXISTS(ordernum);
      
      
      Inner Join SQL SELECT ordernum FROM epicor904.dbo.orderhed;
      
      
      If ScriptErrorCount = 0 then
      STORE Order1 into......;
      Let LastExecTime =  Now()//ThisExecTime
      ;
      End If
      

       

       

       

       

      This is the Log File:

       

      4/30/2012 12:20:46 PM:      Execution started.
      4/30/2012 12:20:46 PM:      QlikView Version:11.00.11282.0
      4/30/2012 12:20:46 PM:      CPU Target                    x64
      4/30/2012 12:20:46 PM:      Operating System              Windows 7 Professional Service Pack 1 (64 bit edition)
      4/30/2012 12:20:46 PM:      Wow64 mode                    Not using Wow64
      4/30/2012 12:20:46 PM:      MDAC Version                  6.1.7601.17514
      4/30/2012 12:20:46 PM:      MDAC Full Install Version     6.1.7601.17514
      4/30/2012 12:20:46 PM:      PreferredCompression          2
      4/30/2012 12:20:46 PM:      EnableParallelReload          1
      4/30/2012 12:20:46 PM:      ParallelizeQvdLoads           1
      4/30/2012 12:20:46 PM:      AutoSaveAfterReload           0
      4/30/2012 12:20:46 PM:      BackupBeforeReload            1
      4/30/2012 12:20:46 PM:      EnableFlushLog                0
      4/30/2012 12:20:46 PM:      SaveInfoWhenSavingFile        0
      4/30/2012 12:20:46 PM:      UserLogfileCharset            0
      4/30/2012 12:20:46 PM:      OdbcLoginTimeout              -1
      4/30/2012 12:20:46 PM:      OdbcConnectionTimeout         -1
      4/30/2012 12:20:46 PM:      ScriptWantsDbWrite            false
      4/30/2012 12:20:46 PM:      ScriptWantsExe                false
      4/30/2012 12:20:46 PM:      LogFile CodePage Used:        1252
      4/30/2012 12:20:46 PM: 0002  SET ThousandSep=','
      4/30/2012 12:20:46 PM: 0003  SET DecimalSep='.'
      4/30/2012 12:20:46 PM: 0004  SET MoneyThousandSep=','
      4/30/2012 12:20:46 PM: 0005  SET MoneyDecimalSep='.'
      4/30/2012 12:20:46 PM: 0006  SET MoneyFormat='$#,##0.00;-$#,##0.00'
      4/30/2012 12:20:46 PM: 0007  SET TimeFormat='h:mm:ss TT'
      4/30/2012 12:20:46 PM: 0008  SET DateFormat='M/D/YYYY'
      4/30/2012 12:20:46 PM: 0009  SET TimestampFormat='M/D/YYYY h:mm:ss[.fff] TT'
      4/30/2012 12:20:46 PM: 0010  SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
      4/30/2012 12:20:46 PM: 0011  SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
      4/30/2012 12:20:46 PM: 0013  Let ThisExecTime = now()
      4/30/2012 12:20:46 PM: 0018  OLEDB CONNECT*Provider*XPassword*
      4/30/2012 12:20:46 PM: 0019  Order1:
      4/30/2012 12:20:46 PM: 0020  
      4/30/2012 12:20:46 PM: 0021      
      4/30/2012 12:20:46 PM: 0022  SQL SELECT 
      4/30/2012 12:20:46 PM: 0023      company,
      4/30/2012 12:20:46 PM: 0024      custnum,
      4/30/2012 12:20:46 PM: 0025      orderdate,
      4/30/2012 12:20:46 PM: 0026      shpconnum,
      4/30/2012 12:20:46 PM: 0027      salesreplist,
      4/30/2012 12:20:46 PM: 0028      openorder,
      4/30/2012 12:20:46 PM: 0029      orderamt,
      4/30/2012 12:20:46 PM: 0030      changedate,
      4/30/2012 12:20:46 PM: 0031      ponum,
      4/30/2012 12:20:46 PM: 0032      shiptonum,
      4/30/2012 12:20:46 PM: 0033      ordernum,
      4/30/2012 12:20:46 PM: 0034      requestdate
      4/30/2012 12:20:46 PM: 0035  FROM epicor904.dbo.orderhed
      4/30/2012 12:20:46 PM: 0036  where voidorder='0' 
      4/30/2012 12:20:46 PM: 0037  and (changedate <= 4/30/2012 12:17:27 PM
      4/30/2012 12:20:46 PM: 0038  AND changedate < 4/30/2012 12:20:46 PM
      4/30/2012 12:20:46 PM: 0039  )
      
      4/30/2012 12:20:46 PM: 0043  Concatenate LOAD company,
      4/30/2012 12:20:46 PM: 0044      custnum,
      4/30/2012 12:20:46 PM: 0045      orderdate,
      4/30/2012 12:20:46 PM: 0046      shpconnum,
      4/30/2012 12:20:46 PM: 0047      salesreplist,
      4/30/2012 12:20:46 PM: 0048      openorder,
      4/30/2012 12:20:46 PM: 0049      orderamt,
      4/30/2012 12:20:46 PM: 0050      changedate,
      4/30/2012 12:20:46 PM: 0051      ponum,
      4/30/2012 12:20:46 PM: 0052      shiptonum,
      4/30/2012 12:20:46 PM: 0053      ordernum,
      4/30/2012 12:20:46 PM: 0054      requestdate FROM .........
      4/30/2012 12:20:46 PM: 0055  WHERE NOT EXISTS(ordernum)
      4/30/2012 12:20:46 PM:           12 fields found: company, custnum, orderdate, shpconnum, salesreplist, openorder, orderamt, changedate, ponum, shiptonum, ordernum, requestdate, General Script Error
      4/30/2012 12:20:46 PM:       Execution Failed
      4/30/2012 12:20:46 PM:      Execution finished.
      

       

       

      If I comment Out and  (changedate <= $(LastExecTime)
      AND changedate < $(ThisExecTime)
      )

       

      then it loads fine. I can't figure out what to fix...

       

      Also if I try in the front end

      if(changedate<=now(),1,0)

      I return 1 for all rows. So it knows it is comparing right during development just just on the SQL select.