Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Error: Cannot insert ...
Microsoft Dynamics GP (Archived)

Error: Cannot insert the value NULL in to column 'XCHGRATE', table 'DBID#.dbo.GL20001'; column does not allow nulls. INSERT fails.

(0) ShareShare
ReportReport
Posted on by Kristie McNulty 977

During our year-end close process, for several databases, we received the following error message in our Check Links report:  Cannot insert the value NULL in to column 'XCHGRATE', table 'DBID#.dbo.GL20001'; column does not allow nulls.  INSERT fails.  When querying the GL20001 table, there were no NULL values.  We had an additional error indicating a currency value had been assigned to the company, but could not locate any activity where that particular currency ID was used.  I've searched all known Knowledge Bases, Blogs, Community, etc., and cannot locate similar reported incidents and their resolutions.  Does anyone know of any other scripts I can run to identify why we received this message during our year-end close / check links process?

*This post is locked for comments

  • Frank_Heslin Profile Picture
    Frank_Heslin 895 on at
    RE: Error: Cannot insert the value NULL in to column 'XCHGRATE', table 'DBID#.dbo.GL20001'; column does not allow nulls. INSERT fails.

    Yes, I think that JE103339 is most likely the issue.  Are you able to see that JE using the JE Inquiry window?  If not, open the General window, like you were doing a manual JE, and click on the magnifying glass next to the JE number, and see if that JE number is there.

    Is the JE valid or did it get 'hung', and then someone created a new JE that 'replaced' JE103339?

    If it's valid, then you need to update the CURRNIDX in GL10000 to EUR.

    If it's not valid, then you need to delete it from every GL table where it exists.

    *** Please make sure you have good backups before updating or deleting and data from the tables. ***

    Here is a query you can use to find which GL tables have a column named 'jrnentry'

    Run this in the Company database where JE103339 was entered.

    *****************************************************************************

    /*

    USE TO FIND WHICH TABLES HAVE A COLUMN WITH SAME NAME

    */

    DECLARE @COLMNAM VARCHAR(255)

    SET @COLMNAM = 'JRNENTRY'

    SELECT * FROM information_schema.columns

    WHERE column_name = @COLMNAM AND TABLE_NAME LIKE 'GL%'

    ORDER BY TABLE_NAME

    *****************************************************************************

    Let us know what you find.

  • Kristie McNulty Profile Picture
    Kristie McNulty 977 on at
    RE: Error: Cannot insert the value NULL in to column 'XCHGRATE', table 'DBID#.dbo.GL20001'; column does not allow nulls. INSERT fails.

    It does, I only wish the error message was more informative, or KB had some helpful guidance on how to resolve.  The only thing I can see is I have one line in the GL10000 table where the CURRNIDX value is 0 instead of the actual ID meant to represent the EUR currency [using JE103339 to refer to "this line"].  All lines in this table, and GL10001, are EUR transactions.  Also worth noting, the one line in GL10000 for JE103339, does not have any corresponding lines in the GL10001, nor does it exist in GL20000 or GL30000.

    Also, for all lines, whether in the header or detail table, the XCHGRATE value is 0, including JE103339.  Which is to be expected, considering the lines are all in the company's functional currency of EUR.

    Could it be the error message we received is misleading and the true issue is the JE103339 line in the header table does not have the correct CURRNIDX value populated?

  • Frank_Heslin Profile Picture
    Frank_Heslin 895 on at
    RE: Error: Cannot insert the value NULL in to column 'XCHGRATE', table 'DBID#.dbo.GL20001'; column does not allow nulls. INSERT fails.

    Hi Kristie,

    It makes sense that you didn't find anything in GL20001 with a NULL XCHGRATE because it's not allowed.  I would check in GL10000 & GL10001 to see if there are any rows with NULL in XCHGRATE.  Since these are WORK tables, there could be an incomplete record (or multiple) that are not needed.

    Let us know what you find.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,280 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,235 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans