This project is read-only.

Synchronisation Issue

Apr 9, 2010 at 12:44 AM
Edited Apr 9, 2010 at 3:00 AM

I am trying to synchronise a Purchase Order list abd get following exception:

Data Synchronization Processing: Purchase Orders System.Data.SqlClient.SqlException: String or binary data would be truncated. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at System.Data.Linq.SqlClient.SqlProvider.Execute(Expression query, QueryInfo queryInfo, IObjectReaderFactory factory, Object[] parentArgs, Object[] userArgs, ICompiledSubQuery[] subQueries, Object lastResult) at System.Data.Linq.SqlClient.SqlProvider.ExecuteAll(Expression query, QueryInfo[] queryInfos, IObjectReaderFactory factory, Object[] userArguments, ICompiledSubQuery[] subQueries) at System.Data.Linq.SqlClient.SqlProvider.System.Data.Linq.Provider.IProvider.Execute(Expression query) at System.Data.Linq.DataContext.ExecuteMethodCall(Object instance, MethodInfo methodInfo, Object[] parameters) at System.Data.Linq.DataContext.ExecuteCommand(String command, Object[] parameters) at AWS.SLAM.Data.SqlDataRepository.InsertItem(String tableSchema, String tableName, Hashtable values) at AWS.SLAM.Data.SqlDataServices.Update(String typeName, String id, Hashtable values) at AWS.SLAM.Events.ListItemUpdater.Update(SPListItem listItem)
9/04/2010 1:55:47 p.m. Information
9/04/2010 1:55:40 p.m. Exception

The Purchase Order list contains a lookup field called JobNo which is the ID of a job list.

  <DataMapping DataSchema="SLAM">
    <List Name="Customers" ActivationOrder="1">
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
 <List Name="Auckland Work In Progress" ActivationOrder="2">
        <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="true"></Field>
        <Field Name="Customer" SPType="Lookup" AssociatedList="Customers" AssociationName="TaskToCustomers"></Field>
  <Field Name="Account Manager" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="Status" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="JobInvoiceNo" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="Invoice Amount" SqlType="Money" SPType="Currency" Required="false"></Field>
  <Field Name="WSR" SqlType="varchar(255)" SPType="Choice" Required="false"></Field>
  <Field Name="Invoice Date" SqlType="datetime" SPType="Date" Required="false"></Field>
  <Field Name="ID" SqlType="int" SPType="number" Required="false"></Field>
 <List Name="Purchase Orders" ActivationOrder="3">
  <Field Name="PO Number" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
        <Field Name="ID" SqlType="int" SPType="number" Required="true"></Field>
  <Field Name="JobNo" SPType="Lookup" AssociatedList="Auckland Work In Progress" AssociationName="POToWIP"></Field>
  <Field Name="PO Type" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="Title" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="Amount" SqlType="Money" SPType="Currency" Required="false"></Field>
  <Field Name="Date" SqlType="datetime" SPType="Date" Required="false"></Field>
  <Field Name="Supplier" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="Supplier Contact" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="Supplier Fax" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="Description" SqlType="varchar(3000)" SPType="Text" Required="false"></Field> 
  <Field Name="Invoiced" SqlType="varchar(255)" SPType="Text" Required="false"></Field>
  <Field Name="Invoiced Amount" SqlType="Money" SPType="Currency" Required="false"></Field>
  <Field Name="Supplier Invoice Date" SqlType="datetime" SPType="Date" Required="false"></Field>
  <Field Name="Supplier Invoice Number" SqlType="varchar(255)" SPType="Text" Required="false"></Field>

Any ideas please...

Apr 9, 2010 at 8:17 AM

The error message "String or binary data would be truncated" is one from the SQL data access layer.  It means that one of your fields in SQL Server is not long enough to hold all of the data that you're trying to stick in it.  Rather than silently truncate (effectively corrupting) your data, an error is thrown.

There are a two ways around this

  1. Make all of your varchar fields really long - much longer than needed.  It seems a lot of yours are already quite long (invoice numbers being 255 characters for example).  The only field that has a different length is "description", presumably for a reason.  I'd try making it longer first.  You could try using varchar(max), which will accept 2GB of data!  Alternatively try something like varchar(8000) - you can't go longer than that. 
  2. Use SQL Profiler to capture the statements being executed by SLAM when attempting to insert the data.  You'll be able to see exactly what is being inserted and how long it really is.  From there you can size your columns appropriately.

The maximum amount of data within a single SQL table row is ~9000 characters (excluding "max" columns).  You should be within that.

Best of luck!