Skip to main content

Notifications

Microsoft Dynamics AX (Archived)

SQL Event ID 117 - String or binary data would be truncated. - On tempdb every 10 minutes.

Posted on by Microsoft Employee

I receive this message in the event log on my aos box four times every 10 minutes (approx).

Object Server 03:  The database reported (session 18 (Admin)): [Microsoft][SQL Server Native Client 10.0][SQL Server]String or binary data would be truncated.. The SQL statement was: "UPDATE T1 SET NAME=T3.NAME,RECVERSION=? FROM tempdb."DBO".t12143_3BB1D74518EC4A90B85299D5A8FF49E4 T1 CROSS JOIN CUSTTABLE T2 CROSS JOIN DIRPARTYTABLE T3 WHERE (T1.PARTITION=?) AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T1.ACCOUNTNUM=T2.ACCOUNTNUM)) AND ((T3.PARTITION=?) AND (T2.PARTY=T3.RECID))"

The session number and the table change each time [dbo.t12143_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx].

There are approx 1500 dbo.t12143_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx tables in the tempdb, each one is empty.

Any idea

1) what process triggers the error.
2) resolution. (i believe that the error is in relation to field length being written to, but this doesnt help as I suspect that these tables are being created).

How do I diagnose this error effectively?

Many thanks

(R2 CU7)

*This post is locked for comments

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: SQL Event ID 117 - String or binary data would be truncated. - On tempdb every 10 minutes.

    Most likely it will not have any other effects, but to make sure everything is okay, you can synch whole DB after changing length. it can reveal all those tables and views which  may have any effect because of length change.

  • Mea_ Profile Picture
    Mea_ 60,278 on at
    RE: SQL Event ID 117 - String or binary data would be truncated. - On tempdb every 10 minutes.

    Hi noooodlez,

    Everything should be ok if you won't copy data from name(100) back to name(60) fields

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL Event ID 117 - String or binary data would be truncated. - On tempdb every 10 minutes.

    Hi Sohaib

    Thanks for the response (and apologies for the delay). You are correct, it would appear to be copying a 100 character srting into a 60 character EDT.

    It makes more sense when you split out the SQL

    UPDATE T1 SET NAME=T3.NAME,RECVERSION=?

    FROM tempdb."DBO".t12143_3BB1D74518EC4A90B85299D5A8FF49E4 T1 (60 character)

           CROSS JOIN CUSTTABLE T2

           CROSS JOIN DIRPARTYTABLE T3 (100 character)

    WHERE (T1.PARTITION=?) AND (((T2.PARTITION=?) AND (T2.DATAAREAID=?)) AND (T1.ACCOUNTNUM=T2.ACCOUNTNUM)) AND ((T3.PARTITION=?) AND (T2.PARTY=T3.RECID))

    So to be clear, if I extend the EDT 'Name' to 100 characters the issue should go away? This wont have any knock on effects will it?

    Many thanks for you help.

    Steven

  • Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: SQL Event ID 117 - String or binary data would be truncated. - On tempdb every 10 minutes.

    Allow me to add here that this issue does not exists in AX2012R3.. its the issue of R2CU7

    the reason is because Name(Field) of CustVendTransAging(Table) has Name(EDT) in AX2012CU7.

    This should be DirPartyName as EDT in properties of Name(Field) of CustVendTransAging(Table)

    So, you have two options

    1) either change EDT in properties of Name(Field) of CustVendTransAging(Table) and update its EDT to DirPartyName

    or

    2) Make length of Name(EDT) to DirPartyName(EDT)

    Personally I would like solution#2 as it will resolve issue in entire AX, which is much better than changing EDT in one Table(CustVendTransAging)

  • Suggested answer
    Sohaib Cheema Profile Picture
    Sohaib Cheema 46,610 User Group Leader on at
    RE: SQL Event ID 117 - String or binary data would be truncated. - On tempdb every 10 minutes.

    check length of DirPartyName(EDT) and Name(EDT)

    Both should be equal in length.

    in your specific case, the origin of issue is in CustVendTransAging(Table)

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!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans