RE: Voucher Transaction error: '', hexadecimal value 0x02, is an invalid character
Hi guys,
I figured out how to fix this issue: Sharing script I used to find the culprit:
select
gje.SUBLEDGERVOUCHER "Voucher",
gje.ACCOUNTINGDATE,
m.MAINACCOUNTID "Account",
m.NAME "AccountName",
gjae.TEXT as Description,CHARINDEX(0x02, CAST(TEXT AS varbinary(30))) Position,
gjae.*
from GENERALJOURNALENTRY gje
inner join ledger com on gje.LEDGER = com.RECID --and gje.[PARTITION] = com.[PARTITION]
inner join GENERALJOURNALACCOUNTENTRY gjae on gje.RECID = gjae.GENERALJOURNALENTRY --and gje.[PARTITION] = gjae.[PARTITION]
inner join DIMENSIONATTRIBUTEVALUECOMBINATION davc on gjae.LEDGERDIMENSION = davc.RECID --and gje.[PARTITION] = davc.[PARTITION]
inner join MAINACCOUNT m on davc.MAINACCOUNT = m.RECID --and davc.[PARTITION] = m.[PARTITION]
inner join [FISCALCALENDARPERIOD] FCP ON gje.[FISCALCALENDARPERIOD] = FCP.[RECID] --and gje.[PARTITION] = FCP.[PARTITION]
inner join [FISCALCALENDARYEAR] FCY ON gje.FISCALCALENDARYEAR = FCY.RECID and gje.[PARTITION] = FCY.[PARTITION] -- AND FCY.FISCALCALENDAR = FCP.FISCALCALENDAR
left join MainAccountCategory mc ON mc.ACCOUNTCATEGORYREF = m.ACCOUNTCATEGORYREF --and mc.[PARTITION] = m.[PARTITION]
left join DIMENSIONATTRIBUTELEVELVALUEVIEW dalvd on davc.recid= dalvd.VALUECOMBINATIONRECID and dalvd.DIMENSIONATTRIBUTE = 5637144826
left join [DIMENSIONATTRIBUTEVALUE] dav on dav.DIMENSIONATTRIBUTE = dalvd.DIMENSIONATTRIBUTE and dav.ENTITYINSTANCE = dalvd.ENTITYINSTANCE
left join [DIMATTRIBUTEOMDEPARTMENT] daod on -- dalvd.DISPLAYVALUE = daod.VALUE
daod.[KEY_] = dav.ENTITYINSTANCE
left join DIMENSIONATTRIBUTELEVELVALUEVIEW dalvf on davc.recid= dalvf.VALUECOMBINATIONRECID and dalvf.DIMENSIONATTRIBUTE = 5637145326
left join [DIMATTRTRANSLFINANCIALTAG] daftf on dalvf.DISPLAYVALUE = daftf.VALUE and daftf.CATEGORY = 5637145076 and daftf.LANGUAGEID = 'en-us'
left join DIMENSIONATTRIBUTELEVELVALUEVIEW dalvp on davc.recid= dalvp.VALUECOMBINATIONRECID and dalvp.DIMENSIONATTRIBUTE = 5637145327
left join [DIMATTRTRANSLFINANCIALTAG] daftp on dalvp.DISPLAYVALUE = daftp.VALUE and daftp.CATEGORY = 5637145077 and daftp.LANGUAGEID = 'en-us'
left join DIMENSIONATTRIBUTELEVELVALUEVIEW dalve on davc.recid= dalve.VALUECOMBINATIONRECID and dalve.DIMENSIONATTRIBUTE = 5637145576
left join SRSANALYSISENUMS enumLPT ON enumLPT.ENUMNAME = 'LedgerPostingType' AND enumLPT.ENUMITEMVALUE = gjae.POSTINGTYPE
left join subledgerVoucherGeneralJournalEntry svgje ON svgje.GENERALJOURNALENTRY = gje.RECID AND svgje.VOUCHERDATAAREAID = gje.SUBLEDGERVOUCHERDATAAREAID AND svgje.VOUCHER = gje.SUBLEDGERVOUCHER
where
gje.ACCOUNTINGDATE BETWEEN '08/01/2020' AND '12/31/2020'
and com.NAME in ('ENTW')
and CHARINDEX(0x02, CAST(TEXT AS varbinary(30))) > 0
order by com.NAME, gje.ACCOUNTINGDATE, FCP.TYPE, gje.JOURNALNUMBER
This is the script I wrote to fix the issue:
update GENERALJOURNALACCOUNTENTRY
set [text] = REPLACE([text], CHAR(2) , '')
where recid in ( <recid>)
I tested this and it works.
