Skip to main content

Notifications

Microsoft Dynamics RMS (Archived)

HQ Client error -2147217873 Cannot insert the value null

Posted on by Microsoft Employee

 

Hi,

Has anyone come across this error before. Recently two of our shops started to show error in 401 worksheet and never get complete.

First it get timeout and then following error shows.

Error-2147217873 - Cannot insert the value Null into column "Quantitycommitted . (screen shots)

 

- I checked Quantitycommitted in store level & itemdynamic in HQ  and there are not Null value in those tables.

0652.error1.JPG

 

*This post is locked for comments

  • Shane O' Malley Profile Picture
    Shane O' Malley 380 on at
    RE: HQ Client error -2147217873 Cannot insert the value null

    Just not sure i see the solution to the problem in the replies above.

    I have the same issue with a new store.   Is there a temp table in the store that i have to clear out or is the problem in HQ.

    Thanks

    Shane

  • Verified answer
    archelle16 Profile Picture
    archelle16 1,735 on at
    RE: HQ Client error -2147217873 Cannot insert the value null

    Oh yeah i got it! right! :D

    You encountered a connection error while creating the temporary table where the data has been selected and uploaded. Since there are no communications between client and server, the transactions rolled back and no temporary table has been created. The time when the server calls the temporary table, there is no such thing as update/insert happened like the one display in the screen but, the server could not find the temporary table.

  • Suggested answer
    archelle16 Profile Picture
    archelle16 1,735 on at
    RE: HQ Client error -2147217873 Cannot insert the value null

    First you need to check what makes the sync throws an error.

    Based on your attachment, you encountered connection error (internet, sql locks etc. etc) which makes transactions failed. Make sure internet are stable and store database are in normal state. Retry sync. If the next error comes out, check or analyze the data itself.

    If you wanna catch the record causes it, you can run series of queries and analyze data.

    Try to run the following query:

    SELECT  ItemLookupCode ,

           Quantity ,

           QuantityCommitted ,

           *

    FROM    Item

    WHERE   DBTimestamp > ( SELECT  MAX(Servertimestamp)

                           FROM    dbo.TimeStampLog

                         )

    Since it was stucked in itemdynamic table, i guess you got a problem while inserting a new item in hq but the so called item got no quantitycommitted which throws this error.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: HQ Client error -2147217873 Cannot insert the value null

    Can you run this SQL in your store database and show us the result?

    Select [ID], ItemLookupcode, [Description],[QuantityCommitted] from [Item]

    where [QuantityCommitted] is null

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: HQ Client error -2147217873 Cannot insert the value null

    I have this error in the syncronization: Error: 2147217873 Cannot insert duplicate key rowin object"dbo Customer"with unique index"IX"_Customer_SocialSecurity" How can I solved this error. Please provide detailed instructions.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: HQ Client error -2147217873 Cannot insert the value null

    also I think it creates a TEMP table and that temp table is not created right.. in the app itself

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: HQ Client error -2147217873 Cannot insert the value null

    yes of course, does restarting SQL server help ?

    from the DBNAME.dob.TABLE I see you could be running SQL 2008 ?

    try a restart and backdate a 401.. does it work once , twice ?

  • Shawn Meade Profile Picture
    Shawn Meade on at
    Re: HQ Client error -2147217873 Cannot insert the value null

    You mentioned that the quantitycommited column has no nulls.  I would like to note that checking this in Store Operations Administrator, any nulls would show up as 0 for SQL columns that are set up as numbers instead of text.  You will need to run the script through SQL Management Studio, or revise the script to look for entries that are not in the QuantityCommitted.

  • Jeff @ Check Point Software Profile Picture
    Jeff @ Check Point ... 13,380 on at
    Re: HQ Client error -2147217873 Cannot insert the value null

    First I would try running a re-index and then a Check in SO and HQ Admin.

    Seems like we saw this in early versions on RMS, what are you running?

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

November Spotlight Star - Khushbu Rajvi

Congratulations to a top community star!

Forum Structure Changes Complete!

🔔 Be sure to subscribe to the new forums you are interested in to stay up to date! 🔔

Dynamics 365 Community Platform update – Oct 28

Welcome to the next edition of the Community Platform Update. This is a status …

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,902 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 229,297 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans