How to solve conversion error when loading from qvd with QlikviewComponents?

Wed, 19 Nov 2014 12:52:33 GMT

Hi,

I'm trying to use an incremental load to reduce my loading time.
To accomplish this I am using Qlikview Components v9.

The very first load is executed succesfully (i.e the first creation of the QVD).

When I reload a second time, I get the following message:

ErrorSource: Microsoft OLE DB Provider for SQL Server, ErrorMsg: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

SQL SELECT *

FROM GTISMVREPL.dbo."JOBCALL_ABNAMRO" WHERE JOBCALL_JOB_DATE >='19-11-14'

How can I resolve this?

My code is as follows:

SET Qvc.Loader.v.QvdDirectory= 'qvd';
LET Qvc.Loader.v.BaseValue = Num(MakeDate(2014));
SET Qvc.Loader.v.ModField.Type=Datetime;
SET Qvc.Loader.v.StoreMaxModFieldValue = 0;
SET Qvc.Loader.v.DatabaseDatetimeMask = 'D-M-Y';

CALL Qvc.IncrementalSetup ('Meldingen_SMV', 'JOBCALL_JOB_DATE', '%Melding.ID');

OLEDB CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=QlikViewABNAMRO;Initial Catalog=GTISMVREPL;Data Source=ARN-BBA-S002;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=N31006334;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is ************);

[$(Qvc.Loader.v.Tablename)]:
LOAD
  AutoNumber(JOBCALL_JOB_NR & ' ' & JOBCALL_JOB_DATE) as %Melding.ID,
  JOBCALL_JOB_DATE,
  JOBCALL_JOB_NR as [Melding.Nummer SMV],
  Date(Floor(JOBCALL_CALL_DATE)) as [Melding.Datum verstuurd],
  Time(Frac(JOBCALL_CALL_DATE)) as [Melding.Tijd verstuurd],
  Timestamp(JOBCALL_CALL_DATE, 'DD-MM-YYYY hh:mm:ss') as [Melding.Moment verstuurd],
  Date(Floor(JOBCALL_REACTION_DATE)) as [Melding.Datum geaccepteerd],
  Time(Frac(JOBCALL_REACTION_DATE)) as [Melding.Tijd geaccepteerd],
  Timestamp(JOBCALL_REACTION_DATE, 'DD-MM-YYYY hh:mm:ss') as [Melding.Moment geaccepteerd];
SQL SELECT *
FROM GTISMVREPL.dbo."JOBCALL_ABNAMRO" WHERE $(Qvc.Loader.v.IncrementalExpression);


CALL Qvc.IncrementalStore


LOAD * 
FROM $(QVD_DIR)\Meldingen_SMV.qvd(qvd);

[Map.Melding.Moment_verstuurd]:
MAPPING LOAD
     [Melding.Nummer SMV],
     [Melding.Moment verstuurd]
RESIDENT [Meldingen_SMV];

Re: How to solve conversion error when loading from qvd with QlikviewComponents?

qlikconsultant — Mon, 24 Nov 2014 12:20:24 GMT

Did you check your SQL Code? Did you run your SQL query on the database?

Re: How to solve conversion error when loading from qvd with QlikviewComponents?

Mon, 24 Nov 2014 12:25:37 GMT

I resolved this.

The following variable:

SET Qvc.Loader.v.DatabaseDatetimeMask = 'D-M-Y';

Defines the literal format that the database uses, not the Qlikview Application.

After removing this line, the error is no more.

topic Re: How to solve conversion error when loading from qvd with QlikviewComponents? in QlikView

How to solve conversion error when loading from qvd with QlikviewComponents?

Re: How to solve conversion error when loading from qvd with QlikviewComponents?

Re: How to solve conversion error when loading from qvd with QlikviewComponents?