title: Weird 'returned data that does not match expected data length for column' error while the expected length is much bigger - SQL SERVER 2012
body: <p>In my project I am rebuilding my Access database to an SQL Database. So to do this I am transferring the Access DATA to the SQL Database. I made sure they both have the same structure and the Access fields are modified correctly in the SQL database. </p>
  
  <p>For most of the data this works. 
  Except for 1 table. This table gives me the following weird error message: </p>
  
  <pre><code>OLE DB provider 'Microsoft.ACE.OLEDB.12.0' for linked server 'OPS_JMD_UPDATE' returned data that does not match expected data length for column '[OPS_JMD_UPDATE]...[OrderStatus].Omschrijving'. The (maximum) expected data length is 100, while the returned data length is 21.
  </code></pre>
  
  <p><strong>So here some more information about both the Access and SQL field/column:</strong></p>
  
  <ul>
  <li>Access type:  Short text </li>
  <li>SQL type: nvarchar(MAX)</li>
  <li>Access column data in it: Normal letters and <code>&amp;</code> <code>-</code> <code>%</code> <code>é</code> <code>+</code> <code>€</code>
  <code>.</code> <code>,</code> <code>:</code> being the 'not normal ones'.</li>
  <li>A few empty Access records (which is allowed)</li>
  <li>A total of 135314 record in the Access table</li>
  </ul>
  
  <p>Iv'e set the SQL datatype to <code>nvarchar(MAX)</code> so that the field can never be to small, this didn't seem to help though..</p>
  
  <p>*The OPS_JMD_UPDATE is the linked Access database</p>
  
  <p>What causes this problem? Is it because some characters aren't allowed or..?  </p>