Replication Services Error: Failed to insert record

Replication Services Error: Failed to insert record

book

Article ID: KB0079035

calendar_today

Updated On:

Products Versions
TIBCO Cloud Integration - Connect ( Scribe ) -

Description

When using MS SQL as a target, Replication Services (RS) fail if data in a field is longer than the target DB schema allows. The full text of the error is Failed to insert record: Record Error Detail: String or binary data would be truncated. The statement has been terminated.

Causes include:
  1. A record in Microsoft Dynamics CRM or Salesforce has data with a greater length than what the CRM or Salesforce schema reports
  2. The schema of the Replication DB has one or more fields with a shorter length than the CRM or Salesforce Schema
  3. A data-type mismatch between the source and target
  4. 3rd party integration or record import into CRM or Salesforce
TIBCO Scribe Online RS uses the CRM or Salesforce schema to create the target database, including the field names and lengths. Consequently, MS SQL throws a truncation error if there is a mismatch. The text of the error doesn't indicate which field caused the error, so some detective work is needed. It is also possible that a truncation error could occur for a Replication Solution using a different source and/or target data source. The debugging steps would be similar.

It is possible to reproduce the issue by adding a custom field in CRM, entering data, then shortening the field (condition 1 above).
 
The steps for CRM are as follows:
  1. Login to Dynamics CRM Online.
  2. Select Settings>Customizations>Customize the System.
  3. Select Entities>Account>Fields>New.
  4. Enter the following:
    • Display name: "Test"
    • Name: "new_Test
    • Maximum Length: 20
  5. Click Save and Close.
  6. Select Forms>Account.
  7. Drag the new field to the Account Information section.
  8. Click Save and Close.
  9. Publish All Customizations.
  10. Open an account and enter 20 characters into the Test field and save the record.
  11. Go back to Settings for the custom field and change field length to 10.
 
 

Issue/Introduction

Use this article to troubleshoot the error below when you are replicating data using a Microsoft SQL Server database as a target: Failed to insert record: String or binary data would be truncated

Resolution

If you already know the entity and field causing the error, skip to Step 4. Otherwise, here's a process to help find the offending field. First, export the schema information from the SQL Server target database to a text file to give you an easy way to sort the fields by length. Then, compare the field lengths, one by one, to the fields in Dynamics CRM or Salesforce. Here are the detailed steps:

Step 1: Find the failed record(s), copy the field names and data to a temporary text file to use for comparison

  1. Login to TIBCO Scribe Online.
  2. Select Solutions.
  3. Locate the RS solution that failed.
  4. From the Gear menu select Execution History.
  5. Locate the Execution with a Fatal Error.
  6. From the Gear menu for that Execution select Copy Error.
  7. Paste the error into a text editor such as NotePad.

Step 2: Export the MSSQL Database Schema of the target DB as a .csv file, to facilitate sorting by field length

  1. Login to SQL Management Studio.
  2. Configure SQL to export headers by selecting Tools > Options > Query Results > SQl Server > Results to Text.
  3. Enable Include columns headers in the result set.
Note: Be sure Query Results = "Results to text".
  1. Restart SQL Management Studio.
  2. Right-click the database being used as the RS destination and select New Query.
  3. Enter the following text:
select * from INFORMATION_SCHEMA.COLUMNS
  1. Click Execute or press (F5).
  2. Right-click in the results section and select Save Results As.
  3. Save the results to a CSV file.
  4. Open the csv and sort by the CHARACTER_MAXIMUM_LENGTH column.
  5. Ignore values of -1. MSSQL uses -1 to indicate MAX_LENGTH.

Step 3: Find the offending field by comparing the database schema to the actual data from the error (in the text editor)

  1. Go line by line in the .csv file, starting with the field with the shortest length (ignoring -1 values).
  2. Search for the each field name in the text editor.
  3. Count the characters in the data to locate the data that is longer than the field length in the schema.
CRM
 
Advanced users comfortable with XML can examine the CRM schema directly, by doing the following:
  1. Copy the xml file located in: C:\Program Files (x86)\Scribe Software\Scribe Online Agent \Connectors\Scribe.Adapter.Crm to your desktop or personal folder to avoid corrupting the original.
  2. Open the XML file using a text editor or browser.
  3. Do a field by field comparison of the schema (XML) with the data.
Salesforce

You can log into Salesforce and locate the field lengths for each field from within their User Interface. Note that Salesforce has two different UI's Lightening and Classic.  Both sets of instructions are included here:

Lightning
 
  1. Login to Salesforce as a user who has permission for Object Manager.
  2. Select Setup from the gear menu.
  3. Select the Object Manager tab.
  4. Select the Object which contains the field in question (i.e. Account).
  5. Select Fields & Relationships to display a list of all fields for the Object. The Data Type column in this view contains the length.  (i.e.  Text(40), URL(255) )
 
Classic
 
  1. Login to Salesforce as a user who has permission for Customize.
  2. Select Setup in the top right corner.
  3. Expand Customize in the Build section of the left hand navigation.
  4. Expand the Object which contains the field in question (i.e. Account).
  5. Select Fields to display a list of all fields for the Object. The Data Type column in this view contains the length.  (i.e.  Text(40), URL(255) )

 

Step 4: Fix it

Once you've identified the entity and field causing the truncation error, the fix depends on your particular situation. If you have access to the replication DB schema and are comfortable with SQL, try option 3 or 4. If you can't change the source data, consider 1 or 3. If a small number of records have bad data that you can alter, try option 2. Consult with your system administrator before making any changes. Exercise caution and take appropriate backups.
 
Option 1: Change the schema in CRM or Salesforce to reflect the correct field size
 - Pros: Data remains unchanged in source
 - Cons: May have unintended effects (reporting, allowing invalid data, etc.)
 
Option 2: Fix the individual record(s) in CRM or Salesforce
 - Pros: Don't need to change the CRM or Salesforce schema
 - Cons: Data will be truncated 
 
Option 3: Change the schema of target MSSQL DB - Increase the size of the offending columns
 - Pros: No changes to Source required
 - Cons: Requires access to the target database Schema. The rules for updating the schema in the target can be reviewed in the TIBCO Scribe Online Help under: Editing The Source Schema
 
Option 4: Drop the entity table (e.g. contacts) from the MSSQL db. Replication Services will do a full data sync for the entity during the next run
 - Pros: No changes to Source required
 - Cons: Requires access to the target database Schema. Will be time consuming re-populate a table with many records

For additional information, see Managing Failed RS Solutions in the TIBCO Scribe Online Help.
Powered by Wolken Software