Hi All,
I hope you are doing well.
I am facing the below error when trying to compare dates in Incremental Load implementation.
2020-03-25 15:45:26 0197 drop table MaxKey
2020-03-25 15:45:26 0202 cp_data2:
2020-03-25 15:45:26 0203 SQL SELECT CraneID,
2020-03-25 15:45:26 0204 cp_id,
2020-03-25 15:45:26 0205 parameter,
2020-03-25 15:45:26 0206 datetime,
2020-03-25 15:45:26 0207 RowID,
2020-03-25 15:45:26 0208 value
2020-03-25 15:45:26 0209 FROM cp_data
2020-03-25 15:45:26 0210
2020-03-25 15:45:26 0211 where datetime> '30/3/2017 6:01:28 AM'
2020-03-25 15:45:26 Error: Connector reply error: 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.
2020-03-25 15:45:28 Execution Failed
2020-03-25 15:45:29 Execution finished.
I am not sure conversion is happening here.
Below is the code I have tried and attached the log file for your reference.
Kindly help me to resolve the issue.
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat='D/M/YYYY';
SET TimestampFormat='D/M/YYYY h:mm:ss[.fff] TT';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
set _qvd_path = '..\..\..\QVD\QVDfolder\'; //?? The QVD filename
set _qvd_name = 'cp_data2'; //?? The name of the QV table we are loading on the Data Load tab
set vKeyField = RowID; //?? Data Primary Key
set vUpdateField = datetime;
OLEDB CONNECT TO ***
let vFileLen = FileSize('$(_qvd_path)$(_qvd_name).qvd');
trace '>> QVD Size: ' $(vFileLen);
if len('$(vFileLen)') = 0 then
trace 'Full reload begins...';
$(_qvd_name):
SQL SELECT
CraneID,
cp_id,
parameter,
datetime,
RowID,
value
FROM cp_data;
let vRows = NoOfRows('$(_qvd_name)');
// Max Date field value into a QVD for the future reload
NewKey:
load
num#(max(FieldValue('$(vUpdateField)', RecNo()))) as Key
AutoGenerate num#(FieldValueCount('$(vUpdateField)'));
let vNewKey = peek('Key', 0, 'NewKey');
let vKeyQVD = 'MaxKey';
$(vKeyQVD):
load
'$(vNewKey)' as MaxKey,
'$(vRows)' as [# of Rows Inserted],
date(now()) as [Reload DateTime]
AutoGenerate(1)
;
STORE $(vKeyQVD) into $(_qvd_path)$(vKeyQVD).qvd(qvd);
drop Table NewKey;
// Store table data into QVD
store $(_qvd_name) into $(_qvd_path)$(_qvd_name).qvd(qvd);
drop table $(_qvd_name);
//5. Reset variables
LET vNewKey = null();
LET vMaxKey = Null();
LET vRows = null();
ELSEIF len('$(vFileLen)') > 0 then
TRACE 'Incremental reload begins...';
set vKeyQVD = 'MaxKey';
$(vKeyQVD):
load
Timestamp(MaxKey) as MaxKey
from
$(_qvd_path)$(vKeyQVD).qvd(qvd);
let vMaxKey = Peek('MaxKey');
trace '>>> Last Max Key value stored: ' & $(vMaxKey);
drop table $(vKeyQVD);
//Perform incremental reload
$(_qvd_name):
SQL SELECT CraneID,
cp_id,
parameter,
datetime,
RowID,
value
FROM cp_data
where $(vUpdateField)> '$(vMaxKey)';
let vRows = NoOfRows('$(_qvd_name)');
trace 'Total Rows:' $(vRows);
Concatenate($(_qvd_name))
load
CraneID,
cp_id,
parameter,
datetime,
RowID,
value
from
$(_qvd_path)$(_qvd_name).qvd(qvd)
Where not Exists ([$(vKeyField)]);
// To delete records inner join key field with the QVD table.
inner join($(_qvd_name))
LOAD
[$(vKeyField)];
sql select [$(vKeyField)]
from cp_data;
trace 'Storing table data into QVD';
store $(_qvd_name) into $(_qvd_path)$(_qvd_name).qvd(qvd);
// 7. Store max Primary Key ID for the future incremental reload (QVD)
NewKey:
load
num#(max(FieldValue('$(vUpdateField)', RecNo()))) as Key
AutoGenerate num#(FieldValueCount('$(vUpdateField)'));
let vNewKey = peek('Key', 0, 'NewKey');
let vKeyQVD = 'MaxKey';
let vRows = NoOfRows('$(_qvd_name)');
$(vKeyQVD):
load
'$(vNewKey)' as MaxKey,
'$(vRows)' as [# of Rows Inserted],
date(now()) as [Reload DateTime]
AutoGenerate(1) ;
drop table NewKey;
STORE $(vKeyQVD) into $(_qvd_path)$(vKeyQVD).qvd(qvd);
//drop table $(_qvd_name);
EXIT SCRIPT;
Thanks in Advance,
Sekhar.
