Unable to insert data into the Channel database – String or Binary data would be truncated
Dynamics AX
2012 R2/R3 Retail
Description:
While inserting a Distribution package job like 1010 into the Channel database following message appears and the operation stops.
Full error message:
Microsoft.Dynamics.Retail.StoreConnect.Request.SQLHandler.ProcessSourceRequestHeaderException:
ProcessTargetRequestHeader failed due to an InvalidOperationException.
connectionString:
Data Source=743R1;Initial Catalog=POSDB;Integrated Security=True;MultipleActiveResultSets=True;Connect
Timeout=60;Encrypt=True;TrustServerCertificate=True;Application
Name=”Commerce Data Exchange Async Client” —>
System.InvalidOperationException: The given value of type String from the data source cannot be
converted to type nvarchar of the specified target column. —>
System.InvalidOperationException: String or binary data would be truncated.
at
System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData
metadata, Boolean isNull, Boolean& isSqlType, Boolean&
coercedToDataFeed)
— End of inner exception stack trace —
How to troubleshoot the issue:
The message “String and Binary data would betruncated”, means that the data length of a string is longer than accepted
by the field.
Validate which field is longer than accepted by
- Copy your Channel database to the same server as your AX database
- Change the AX database name in the script attached
- Open a new Query on the Channel database and execute the statement
Result:
The result shows us that the Dirparytable NameAlias has a record that is 45
characters in length in the AX database, but only 20 characters are allowed in
the channel database.
tableName |
ColumnName |
CHANNELLEN |
HQMAXLEN |
DIRPARTYTABLE |
NAMEALIAS |
20 |
45 |
NUMBERSEQUENCETABLE |
TXT |
30 |
35 |
BARCODESETUP |
DESCRIPTION |
30 |
26 |
RETAILTENDERTYPETABLE |
NAME |
30 |
23 |
RETAILSIZEGROUPTABLE |
DESCRIPTION |
30 |
22 |
INVENTMODELGROUP |
NAME |
30 |
16 |
RETAILSTYLEGROUPTABLE |
DESCRIPTION |
30 |
15 |
CASHDISC |
DESCRIPTION |
30 |
11 |
The solution:
- You can change the schema in the channel database to accept the needed input
- Retail SDK/CreateDatabase.dll if done correctly
- Or you can rename the value in AX to only have 20 characters
Author: Kim
Truelsen
Date:
15/5-2015
MS_RETAIL_MAX_LENHTH_RETAIL_DIFF.sql
We're always looking for feedback and would like to hear from you. Please head to the Dynamics 365 Community to start a discussion, ask questions, and tell us what you think!