I was getting the below error while running "Transaction list by date" report in GL module. And this error is coming when I run the report for a specific date.
INSERT INTO tempdb."DBO".t9593_59B28DE6110C4D489ED985CE004FD381 (GENERALJOURNALACCOUNTENTRY,TXT,CURRENCYCODE,POSTINGTYPE,ISCREDIT,TRANSACTIONCURRENCYAMOUNTDEBIT,ACCOUNTINGCURRENCYAMOUNTDEBIT,REPORTINGCURRENCYAMOUNTDEBIT,ACCOUNTNUM,ACCOUNTNAME,DIMENSION,MAINACCOUNT,GENERALJOURNALENTRY,TRANSDATE,JOURNALNUMBER,VOUCHER,ACKNOWLEDGEMENTDATE,GENERALJOURNALENTRYDATAAREA,RECVERSION,PARTITION) SELECT T1.RECID AS f1,T1.TEXT AS f2,T1.TRANSACTIONCURRENCYCODE AS f3,T1.POSTINGTYPE AS f4,T1.ISCREDIT AS f5,T1.TRANSACTIONCURRENCYAMOUNT AS f6,T1.ACCOUNTINGCURRENCYAMOUNT AS f7,T1.REPORTINGCURRENCYAMOUNT AS f8,T2.MAINACCOUNT AS f9,T2.NAME AS f10,T2.FULLYQUALIFIEDACCOUNT AS f11,T2.MAINACCOUNTRECID AS f12,T3.RECID AS f13,T3.ACCOUNTINGDATE AS f14,T3.JOURNALNUMBER AS f15,T3.SUBLEDGERVOUCHER AS f16,T3.ACKNOWLEDGEMENTDATE AS f17,T5.DATAAREA AS f18,1 AS RECVERSION,5637144576 AS PARTITION FROM GENERALJOURNALACCOUNTENTRY T1 CROSS JOIN MAINACCOUNTLEDGERDIMENSIONVIEW T2 CROSS JOIN GENERALJOURNALENTRY T3 CROSS JOIN LEDGER T4 CROSS JOIN DIRPARTYTABLE T5 WHERE (T1.PARTITION=5637144576) AND (((T2.PARTITION=5637144576) AND (T2.PARTITION#2=5637144576)) AND ((T2.MAINACCOUNT=N'720115') AND (T1.LEDGERDIMENSION=T2.LEDGERDIMENSIONID))) AND ((T3.PARTITION=5637144576) AND ((((T3.ACCOUNTINGDATE>={ts '2019-03-30 00:00:00.000'}) AND (T3.ACCOUNTINGDATE<={ts '2019-03-30 00:00:00.000'})) AND (T3.LEDGER=5637145326)) AND (T1.GENERALJOURNALENTRY=T3.RECID))) AND ((T4.PARTITION=5637144576) AND (T3.LEDGER=T4.RECID)) AND (((T5.PARTITION=5637144576) AND (T4.PRIMARYFORLEGALENTITY=T5.RECID)) AND (T5.INSTANCERELATIONTYPE IN (10552) )) ORDER BY T2.MAINACCOUNT
Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated.
Cannot execute a data definition language command on Ledger transaction list (LedgerTransactionListStagingTmp). Date: . The SQL database has issued an error.
Reason: After doing some research I identified that the problem is with the size of "Txt" field in the tmp table is causing the issue. The length of this field is 50 in the report(tmp table) and the "description" in the transaction is more than 50 characters and it causing the SQL field length mismatch issue.
Solution: In my case I increased the length if EDT field for "txt" in temp table to 512.
That's it.
@Rahul
INSERT INTO tempdb."DBO".t9593_59B28DE6110C4D489ED985CE004FD381 (GENERALJOURNALACCOUNTENTRY,TXT,CURRENCYCODE,POSTINGTYPE,ISCREDIT,TRANSACTIONCURRENCYAMOUNTDEBIT,ACCOUNTINGCURRENCYAMOUNTDEBIT,REPORTINGCURRENCYAMOUNTDEBIT,ACCOUNTNUM,ACCOUNTNAME,DIMENSION,MAINACCOUNT,GENERALJOURNALENTRY,TRANSDATE,JOURNALNUMBER,VOUCHER,ACKNOWLEDGEMENTDATE,GENERALJOURNALENTRYDATAAREA,RECVERSION,PARTITION) SELECT T1.RECID AS f1,T1.TEXT AS f2,T1.TRANSACTIONCURRENCYCODE AS f3,T1.POSTINGTYPE AS f4,T1.ISCREDIT AS f5,T1.TRANSACTIONCURRENCYAMOUNT AS f6,T1.ACCOUNTINGCURRENCYAMOUNT AS f7,T1.REPORTINGCURRENCYAMOUNT AS f8,T2.MAINACCOUNT AS f9,T2.NAME AS f10,T2.FULLYQUALIFIEDACCOUNT AS f11,T2.MAINACCOUNTRECID AS f12,T3.RECID AS f13,T3.ACCOUNTINGDATE AS f14,T3.JOURNALNUMBER AS f15,T3.SUBLEDGERVOUCHER AS f16,T3.ACKNOWLEDGEMENTDATE AS f17,T5.DATAAREA AS f18,1 AS RECVERSION,5637144576 AS PARTITION FROM GENERALJOURNALACCOUNTENTRY T1 CROSS JOIN MAINACCOUNTLEDGERDIMENSIONVIEW T2 CROSS JOIN GENERALJOURNALENTRY T3 CROSS JOIN LEDGER T4 CROSS JOIN DIRPARTYTABLE T5 WHERE (T1.PARTITION=5637144576) AND (((T2.PARTITION=5637144576) AND (T2.PARTITION#2=5637144576)) AND ((T2.MAINACCOUNT=N'720115') AND (T1.LEDGERDIMENSION=T2.LEDGERDIMENSIONID))) AND ((T3.PARTITION=5637144576) AND ((((T3.ACCOUNTINGDATE>={ts '2019-03-30 00:00:00.000'}) AND (T3.ACCOUNTINGDATE<={ts '2019-03-30 00:00:00.000'})) AND (T3.LEDGER=5637145326)) AND (T1.GENERALJOURNALENTRY=T3.RECID))) AND ((T4.PARTITION=5637144576) AND (T3.LEDGER=T4.RECID)) AND (((T5.PARTITION=5637144576) AND (T4.PRIMARYFORLEGALENTITY=T5.RECID)) AND (T5.INSTANCERELATIONTYPE IN (10552) )) ORDER BY T2.MAINACCOUNT
Microsoft][ODBC Driver 17 for SQL Server][SQL Server]String or binary data would be truncated.
Cannot execute a data definition language command on Ledger transaction list (LedgerTransactionListStagingTmp). Date: . The SQL database has issued an error.
Reason: After doing some research I identified that the problem is with the size of "Txt" field in the tmp table is causing the issue. The length of this field is 50 in the report(tmp table) and the "description" in the transaction is more than 50 characters and it causing the SQL field length mismatch issue.
Solution: In my case I increased the length if EDT field for "txt" in temp table to 512.
That's it.
@Rahul
No comments:
Post a Comment