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

(0) Share

Report

Posted on by Community Member 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

All responses (5)

Answers (0)

Sohaib Cheema 46,610 User Group Leader on at

Like (0)

Report

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_ 60,278 on at

Like (0)

Report

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 Microsoft Employee on at

Like (0)

Report

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 46,610 User Group Leader on at

Like (0)

Report

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 46,610 User Group Leader on at

Like (1)

Report

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)