Hello,
We've seen errors similar to this in the past caused when there is a transaction entered for a year that is out of range. Example - the year was entered as 1802. To find out if this is the case you can use SQL Server Management Studio to look at the RM20101 Due Date, Document Date, and Discount Date.
a. To find the out of range date, you execute the following scripts in SQL. One set is for the Open Table
PRINT 'RM20101 DocDate'
PRINT '----'
SELECT * from RM20101 where DOCDATE<'1900-01-01' or DOCDATE >'2065-12-31'
PRINT 'RM20101 Due Date'
PRINT '----'
SELECT * from RM20101 where DUEDATE<'1900-01-01' or DUEDATE>'2065-12-31'
PRINT 'RM20101 Discount Date'
PRINT '----'
SELECT * from RM20101 where DISCDATE<'1900-01-01' or DISCDATE>'2065-12-31'
PRINT 'RM20101 GLPostingDate'
PRINT '----'
SELECT * from RM20101 where GLPOSTDT<'1900-01-01' or GLPOSTDT>'2065-12-31'
b. The Script Results Below will show records in the History table.
PRINT 'RM30101 DocDate'
PRINT '----'
SELECT * from RM30101 where DOCDATE<'1900-01-01' or DOCDATE >'2065-12-31'
PRINT 'RM30101 Due Date'
PRINT '----'
SELECT * from RM30101 where DUEDATE<'1900-01-01' or DUEDATE>'2065-12-31'
PRINT 'RM30101 Discount Date'
PRINT '----'
SELECT * from RM30101 where DISCDATE<'1900-01-01' or DISCDATE>'2065-12-31'
PRINT 'RM30101 GLPostingDate'
PRINT '----'
SELECT * from RM30101 where GLPOSTDT<'1900-01-01' or GLPOSTDT>'2065-12-31'
c. If any results from the above step, use a similar SQL statement below to update with the row with the correct date to reflect the correct year. (**MAKE SURE TO HAVE A CURRENT AND RESTORABLE BACKUP**)
Example: UPDATE TTTTT SET DDDDD = 'XXXX' WHERE DEX_ROW_ID = YYYY
**Replace TTTTT with the correct table name such as RM20101 or RM30101, replace DDDD with the column name such as DOCDATE, DUEDATE, GLPOSTDT or DISCDATE, replace XXXX with the correct transaction date and Replace YYYY with the Dex_Row_ID value.
Another option we can do is grab a DEXSQL.log of the error message in Dynamics GP. This should provide information on which record is causing the issue. I have attached a kb article for the DEXSQL.log below.
850996 How to create a Dexsql.log file to troubleshoot error messages in Microsoft Dynamics GP
support.microsoft.com/.../en-US
Thanks
Share
Report
All responses (
Answers (
