Solved: Re: Connector reply error - Qlik Community

vasavidxc · ‎2020-04-26

Hi Team,

I am getting below error for few generators in my project and if I do a full refresh(delete and refresh qvd) qvd getting success. Otherwise, the qvd generator keeps on failing.

Error: Connector reply error: SQL##f - SqlState: 22007, ErrorCode: 3679, ErrorMsg: ERROR 3679: Invalid input syntax for timestamp: ""

How to solve this issue, why I am getting this error.

Please let me know the issue and how to solve the issue.

Thanks,

Vasavi.

bhaskar_sm · ‎2020-04-27

Hi,

its Qlikview related timestamp variable vIncrementalExpression is not getting populated correctly.

if check the log you can see that, zero lines fatched from EGIT-GSD\GSD_Click_Audit.qvd] (qvd) --> 2020-04-26 10:12:03 0 lines fetched

2020-04-26 10:11:57 0134 LOAD max(Timestamp(Update_GMT_Timestamp)) as maxdate
2020-04-26 10:11:57 0135 FROM [..\..\..\..\..\Enterprise-Group\QVD\WW\EGIT-GSD\GSD_Click_Audit.qvd] (qvd)
2020-04-26 10:12:03 1 fields found: maxdate,
2020-04-26 10:12:03 0 lines fetched
2020-04-26 10:12:03 0136 Let vIncrementalExpression = 'WHERE ' & 'Update_GMT_Timestamp' & ' > ' & chr(39) & peek('maxdate') & chr(39)

please correct me if I have understood incorreclty.

Thanks,

Bhaskar

View solution in original post

bhaskar_sm · ‎2020-04-26

Hi,

I think you might need to check the variable values, where you have timestamp defined. it's not getting populated correctly that's why you getting this error message. Please check out below link for your reference.

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ErrorCodes/SqlState-22007.htm

Thanks,

BK

vasavidxc · ‎2020-04-26

Hi @bhaskar_sm ,

Thanks for your response, is it related to DB timestamp or QlikView related timestamp.

Please check my log file generated below(as i am unable to attach log file)

<2020-04-26 10:11:48 Execution started.
2020-04-26 10:11:48 QVB Version 12.30.20300+2019-05-14 14:44:03+87e7495
2020-04-26 10:11:48 CPU Target x64
2020-04-26 10:11:48 Operating System Windows Server 2012 R2 Datacenter (64 bit edition)
2020-04-26 10:11:48 Wow64 mode Not using Wow64
2020-04-26 10:11:48 Language 0409 English English
2020-04-26 10:11:48 Country USA United States United States
2020-04-26 10:11:48 MDAC Version 6.3.9600.16384
2020-04-26 10:11:48 MDAC Full Install Version 6.3.9600.16384
2020-04-26 10:11:48 PreferredCompression 2
2020-04-26 10:11:48 EnableParallelReload 1
2020-04-26 10:11:48 ParallelizeQvdLoads 1
2020-04-26 10:11:48 AutoSaveAfterReload 0
2020-04-26 10:11:48 BackupBeforeReload 0
2020-04-26 10:11:48 EnableFlushLog 0
2020-04-26 10:11:48 SaveInfoWhenSavingFile 0
2020-04-26 10:11:48 UserLogfileCharset 65001
2020-04-26 10:11:48 OdbcLoginTimeout -1
2020-04-26 10:11:48 OdbcConnectionTimeout -1
2020-04-26 10:11:48 LongestPossibleLine 1048576
2020-04-26 10:11:48 MaxCoreMask -1
2020-04-26 10:11:48 ScriptWantsDbWrite false
2020-04-26 10:11:48 ScriptWantsExe false
2020-04-26 10:11:48 ICU_FOR_CHARACTER_CLASSIFICATION true
2020-04-26 10:11:48 ICU_FOR_CODE_PAGES true
2020-04-26 10:11:48 ICU_FOR_COLLATION true
2020-04-26 10:11:48 ICU_FOR_LOCALE_INFO true
2020-04-26 10:11:48 ICU_FOR_SUPPORT_INFO true
2020-04-26 10:11:48 ICU_FOR_TIME_ZONE_INFO true
2020-04-26 10:11:48 LogFile CodePage Used: 65001
2020-04-26 10:11:48 UseMemoryFillPattern 1
2020-04-26 10:11:48 ScriptMode Legacy (gives greater system access) (aka qv11 mode)
2020-04-26 10:11:48 SequentialThreadCount 75
2020-04-26 10:11:48 NumberOfCores (effective) 24
2020-04-26 10:11:48 ReloadCodebase Bnf
2020-04-26 10:11:49 Reload Executed By AMERICAS\$qlikviewpro001
2020-04-26 10:11:49 Process Executing QVB
2020-04-26 10:11:49 Process ID 336776
2020-04-26 10:11:49 Grpc version 5.0.0
2020-04-26 10:11:49 Protobuf version 3.4.0
2020-04-26 10:11:49 0002 SET ThousandSep=','
2020-04-26 10:11:49 0003 SET DecimalSep='.'
2020-04-26 10:11:49 0004 SET MoneyThousandSep=','
2020-04-26 10:11:49 0005 SET MoneyDecimalSep='.'
2020-04-26 10:11:49 0006 SET MoneyFormat='$#,##0.00;($#,##0.00)'
2020-04-26 10:11:49 0007 SET TimeFormat='h:mm:ss TT'
2020-04-26 10:11:49 0008 SET DateFormat='M/D/YYYY'
2020-04-26 10:11:49 0009 SET TimestampFormat='M/D/YYYY h:mm:ss'
2020-04-26 10:11:49 0010 SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec'
2020-04-26 10:11:49 0011 SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun'
2020-04-26 10:11:49 0012 SET NullValue = '#'
2020-04-26 10:11:49 0014 NULLASVALUE *
2020-04-26 10:11:49 0020 ODBC CONNECT TO *** (XUserId is ***, XPassword is ***)
2020-04-26 10:11:54 0022 SET QVD_Path = '..\..\..\..\..\Enterprise-Group\QVD\WW\EGIT-GSD'
2020-04-26 10:11:54 0026 LOAD @1 as vdbname
2020-04-26 10:11:54 0027 FROM
2020-04-26 10:11:54 0028 ..\..\..\..\..\Enterprise-Group\Include\WW\EGIT-GSD\__DatabaseName__.txt
2020-04-26 10:11:54 0029 (txt, codepage is 1252, no labels, delimiter is '\t', msq)
2020-04-26 10:11:54 1 fields found: vdbname,
2020-04-26 10:11:54 1 lines fetched
2020-04-26 10:11:54 0009 Let vdb = peek('vdbname')
2020-04-26 10:11:54 0027 LOAD @1 as vSchema
2020-04-26 10:11:54 0028 FROM
2020-04-26 10:11:54 0029 ..\..\..\..\..\Enterprise-Group\Include\WW\EGIT-GSD\schemaname.txt
2020-04-26 10:11:54 0030 (txt, codepage is 1252, no labels, delimiter is '\t', msq)
2020-04-26 10:11:54 1 fields found: vSchema,
2020-04-26 10:11:54 1 lines fetched
2020-04-26 10:11:54 0009 Let vSchemaName = peek('vSchema')
2020-04-26 10:11:54 0034 Click_Engineer:
2020-04-26 10:11:54 0035
2020-04-26 10:11:54 0036 SQL SELECT *
2020-04-26 10:11:54 0037 FROM shr4_vrt_pro_005."egig_Core_Common_Service_Delivery"."Click_Engineer"
2020-04-26 10:11:56 17 fields found: RecordIdentifier, Name, AuditTimeStamp, AuditAction, ObjectKey, EngineerName, EngineerID, EngineerCity, EngineerActive, EngineerDistrict, MaxDistanceFromHB, EngineerSkills, EngineerRegion, EngineerCalendar, ChangeDescription, Insert_GMT_Timestamp, Update_GMT_Timestamp,
2020-04-26 10:11:56 56,489 lines fetched
2020-04-26 10:11:56 0039 STORE Click_Engineer into ..\..\..\..\..\Enterprise-Group\QVD\WW\EGIT-GSD/GSD_Click_Engineer.qvd(qvd)
2020-04-26 10:11:57 0041 DROP Table Click_Engineer
2020-04-26 10:11:57 0050 SET vClickAudit =
2020-04-26 10:11:57 0051 'AssignedEngineers,
2020-04-26 10:11:57 0052 AssignmentComment,
2020-04-26 10:11:57 0053 AssignmentFinish,
2020-04-26 10:11:57 0054 AssignmentStart,
2020-04-26 10:11:57 0055 AuditAction,
2020-04-26 10:11:57 0056 AuditTimeStamp,
2020-04-26 10:11:57 0057 ChangeDescription,
2020-04-26 10:11:57 0058 EscalatedEngineers,
2020-04-26 10:11:57 0059 EscalationManagerLevel,
2020-04-26 10:11:57 0060 EscalationReasonCode,
2020-04-26 10:11:57 0061 InJeopardy,
2020-04-26 10:11:57 0062 Insert_GMT_Timestamp,
2020-04-26 10:11:57 0063 IsEscalated,
2020-04-26 10:11:57 0064 IsRuleViolating,
2020-04-26 10:11:57 0065 IsSiteTask,
2020-04-26 10:11:57 0066 IsTaskLocked,
2020-04-26 10:11:57 0067 JeopardyState,
2020-04-26 10:11:57 0068 Name,
2020-04-26 10:11:57 0069 ObjectType,
2020-04-26 10:11:57 0070 PartOrderRequired,
2020-04-26 10:11:57 0071 RecordIdentifier,
2020-04-26 10:11:57 0072 RescheduleAnyButMe,
2020-04-26 10:11:57 0073 RescheduleAnyone,
2020-04-26 10:11:57 0074 RescheduleDispReview,
2020-04-26 10:11:57 0075 RescheduleNotes,
2020-04-26 10:11:57 0076 RuleViolationText,
2020-04-26 10:11:57 0077 TaskCallID,
2020-04-26 10:11:57 0078 TaskDueDate,
2020-04-26 10:11:57 0079 TaskDuration,
2020-04-26 10:11:57 0080 TaskKey,
2020-04-26 10:11:57 0081 TaskLockEnd,
2020-04-26 10:11:57 0082 TaskLockStart,
2020-04-26 10:11:57 0083 TaskNumber,
2020-04-26 10:11:57 0084 TaskPinned,
2020-04-26 10:11:57 0085 TaskStatus,
2020-04-26 10:11:57 0086 TaskType,
2020-04-26 10:11:57 0087 Update_GMT_Timestamp'
2020-04-26 10:11:57 0094 TableList:
2020-04-26 10:11:57 0095
2020-04-26 10:11:57 0096 LOAD * INLINE
2020-04-26 10:11:57 0097 [
2020-04-26 10:11:57 0098 TableName,DeltaField,ID,FileName,Select,
2020-04-26 10:11:57 0099 Click_Audit,Update_GMT_Timestamp,RecordIdentifier,GSD_Click_Audit,vClickAudit
2020-04-26 10:11:57 0100 ]
2020-04-26 10:11:57 5 fields found: TableName, DeltaField, ID, FileName, Select,
2020-04-26 10:11:57 1 lines fetched
2020-04-26 10:11:57 0104 Let vTableCount = NoOfRows('TableList')
2020-04-26 10:11:57 0112 SUB LoadData
2020-04-26 10:11:57 0173 Call LoadData
2020-04-26 10:11:57 0114 For i = 0 To 1 -1
2020-04-26 10:11:57 0115 LET vTableName = Peek('TableName', 0, 'TableList')
2020-04-26 10:11:57 0116 LET vDeltaField = Peek('DeltaField', 0, 'TableList')
2020-04-26 10:11:57 0117 LET vID = Peek('ID', 0, 'TableList')
2020-04-26 10:11:57 0118 LET vQvdFile= Peek('FileName',0,'TableList')
2020-04-26 10:11:57 0119 LET vSelect = Peek('Select',0,'TableList')
2020-04-26 10:11:57 0120 LET vSelect= vClickAudit
2020-04-26 10:11:57 0122 SET vQvdFile = GSD_Click_Audit.qvd
2020-04-26 10:11:57 0124 LET vQvdExists = if(QvdNoOfRecords('..\..\..\..\..\Enterprise-Group\QVD\WW\EGIT-GSD\GSD_Click_Audit.qvd') > 0, -1, 0)
2020-04-26 10:11:57 0128 IF -1 THEN
2020-04-26 10:11:57 0130 maxdateTab:
2020-04-26 10:11:57 0131
2020-04-26 10:11:57 0132
2020-04-26 10:11:57 0133
2020-04-26 10:11:57 0134 LOAD max(Timestamp(Update_GMT_Timestamp)) as maxdate
2020-04-26 10:11:57 0135 FROM [..\..\..\..\..\Enterprise-Group\QVD\WW\EGIT-GSD\GSD_Click_Audit.qvd] (qvd)
2020-04-26 10:12:03 1 fields found: maxdate,
2020-04-26 10:12:03 0 lines fetched
2020-04-26 10:12:03 0136 Let vIncrementalExpression = 'WHERE ' & 'Update_GMT_Timestamp' & ' > ' & chr(39) & peek('maxdate') & chr(39)
2020-04-26 10:12:03 0137 DROP table maxdateTab
2020-04-26 10:12:03 0140 ELSE
2020-04-26 10:12:03 0146 IF -1 THEN
2020-04-26 10:12:03 0147 Click_Audit:
2020-04-26 10:12:03 0148 LOAD AssignedEngineers,
2020-04-26 10:12:03 0149 AssignmentComment,
2020-04-26 10:12:03 0150 AssignmentFinish,
2020-04-26 10:12:03 0151 AssignmentStart,
2020-04-26 10:12:03 0152 AuditAction,
2020-04-26 10:12:03 0153 AuditTimeStamp,
2020-04-26 10:12:03 0154 ChangeDescription,
2020-04-26 10:12:03 0155 EscalatedEngineers,
2020-04-26 10:12:03 0156 EscalationManagerLevel,
2020-04-26 10:12:03 0157 EscalationReasonCode,
2020-04-26 10:12:03 0158 InJeopardy,
2020-04-26 10:12:03 0159 Insert_GMT_Timestamp,
2020-04-26 10:12:03 0160 IsEscalated,
2020-04-26 10:12:03 0161 IsRuleViolating,
2020-04-26 10:12:03 0162 IsSiteTask,
2020-04-26 10:12:03 0163 IsTaskLocked,
2020-04-26 10:12:03 0164 JeopardyState,
2020-04-26 10:12:03 0165 Name,
2020-04-26 10:12:03 0166 ObjectType,
2020-04-26 10:12:03 0167 PartOrderRequired,
2020-04-26 10:12:03 0168 RecordIdentifier,
2020-04-26 10:12:03 0169 RescheduleAnyButMe,
2020-04-26 10:12:03 0170 RescheduleAnyone,
2020-04-26 10:12:03 0171 RescheduleDispReview,
2020-04-26 10:12:03 0172 RescheduleNotes,
2020-04-26 10:12:03 0173 RuleViolationText,
2020-04-26 10:12:03 0174 TaskCallID,
2020-04-26 10:12:03 0175 TaskDueDate,
2020-04-26 10:12:03 0176 TaskDuration,
2020-04-26 10:12:03 0177 TaskKey,
2020-04-26 10:12:03 0178 TaskLockEnd,
2020-04-26 10:12:03 0179 TaskLockStart,
2020-04-26 10:12:03 0180 TaskNumber,
2020-04-26 10:12:03 0181 TaskPinned,
2020-04-26 10:12:03 0182 TaskStatus,
2020-04-26 10:12:03 0183 TaskType,
2020-04-26 10:12:03 0184 Update_GMT_Timestamp
2020-04-26 10:12:03 0149 SQL SELECT *
2020-04-26 10:12:03 0150 FROM shr4_vrt_pro_005.egig_Core_Common_Service_Delivery.Click_Audit WHERE Update_GMT_Timestamp > ''
2020-04-26 10:12:03 Error: Connector reply error: SQL##f - SqlState: 22007, ErrorCode: 3679, ErrorMsg: ERROR 3679: Invalid input syntax for timestamp: ""
2020-04-26 10:12:03
2020-04-26 10:12:03 Execution Failed
2020-04-26 10:12:04 Execution finished.>

Kindly check and let me know.

Thanks in advance.

bhaskar_sm · ‎2020-04-27

Hi,

its Qlikview related timestamp variable vIncrementalExpression is not getting populated correctly.

if check the log you can see that, zero lines fatched from EGIT-GSD\GSD_Click_Audit.qvd] (qvd) --> 2020-04-26 10:12:03 0 lines fetched

2020-04-26 10:11:57 0134 LOAD max(Timestamp(Update_GMT_Timestamp)) as maxdate
2020-04-26 10:11:57 0135 FROM [..\..\..\..\..\Enterprise-Group\QVD\WW\EGIT-GSD\GSD_Click_Audit.qvd] (qvd)
2020-04-26 10:12:03 1 fields found: maxdate,
2020-04-26 10:12:03 0 lines fetched
2020-04-26 10:12:03 0136 Let vIncrementalExpression = 'WHERE ' & 'Update_GMT_Timestamp' & ' > ' & chr(39) & peek('maxdate') & chr(39)

please correct me if I have understood incorreclty.

Thanks,

Bhaskar