title: How to migrate datetime to datetime2 with new SQL Server Native Client
body: <p>We are currently migrating our database from <code>datetime</code>to <code>datetime2</code> including SQL Server Native Client v11 (SQL Server 2012). The database update was easily done, but the problems come along with the new SQL Server Native Client 11 we want to use. </p>
  
  <p>In general we have OLE DB Consumers with "COLUMN_ENTRY*" accessors for our CRUD operations in place. For the <code>datetime2</code> columns the members have the type <code>DBTIMESTAMP</code>. With the <code>SQLNCLI</code> provider the fraction part of the <code>DBTIMESTAMP</code> was silently truncated to the supported value. With the <code>SQLNCLI11</code> an insert with too precise fraction results in this error: </p>
  
  <p><code>DB_E_ERRORSOCCURRED Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.</code></p>
  
  <p>According to <a href="http://www.adopenstatic.com/faq/80040e21.asp" rel="nofollow">this link</a>, this error is returned if too much data is being inserted into a field. From this I assumed that the fractional part of the DBDATETIME member is too precise to be inserted. According to <a href="https://connect.microsoft.com/SQLServer/feedback/details/330168/command-execute-using-sqlncli10-ole-db-with-parameter-binding-fails-if-datetime-fraction-0" rel="nofollow">this link</a>, the new Native Client Versions (10 and 11) do not truncate but fail in an error. Take this simplified example what we want to achieve:</p>
  
  <pre class="lang-cpp prettyprint-override"><code>class CMyTableStruct
  {
  public: 
      CMyTableStruct();
      LONG m_lID;
      DBTIMESTAMP m_dtLogTime;
  };
  class CMyTableStruct_InsertAccessor : public CMyTableStruct
  {
  public:
      BEGIN_PARAM_MAP(CMyTableStruct_InsertAccessor)
          COLUMN_ENTRY(1,  m_dtLogTime)
      END_PARAM_MAP()  
  };
  </code></pre>
  
  <p>At some part in the code I initialize the timestamp to 2015-08-10 07:47:49.986149999 and the insert fails. If I reset the fraction to 0, the insert works; any value beside 0 fails. I tried to supply the datetime precision and scale using <code>COLUMN_ENTRY_PS</code> with various values, but the inserts always failed. </p>
  
  <p>How can we force the Native Client to simply accept the value and truncate it? It's quite obvious that we cannot truncate all the values manually to the supported DB precision. I couldn't find any proper documentation about how to use datetime2 with the new Native Client. Are we missing any conversion or setting to handle datetime2 correctly? </p>
  
  <p>This is the test setup:</p>
  
  <ul>
  <li>Windows 7 64bit</li>
  <li>SQL Server Native Client 11 (SQLNCLI11)</li>
  <li>SQL Server 2012 </li>
  <li>DBPROP_INIT_LCID=2055</li>
  <li>DBPROP_INIT_PROMPT=4</li>
  <li>DBPROP_INIT_DATASOURCE=localhost</li>
  <li>DBPROP_AUTH_INTEGRATED=SSPI</li>
  </ul>
  
  <p>With the <code>SQLNCLI</code> provider the same code works. </p>
  
  <p>[EDIT#1]: I dumped some more error information using <code>AtlTraceErrorRecords</code> and this confirmed the same error as in the linked Microsoft Connect report: </p>
  
  <p><code>Row #:    0 Source: "Microsoft SQL Server Native Client 11.0" Description: "The fractional part of the provided time value overflows the scale of the corresponding SQL Server parameter or column. Increase bScale in DBPARAMBINDINFO or column scale to correct this error." Help File: "(null)" Help Context:    0 GUID: {0C733A63-2A1C-11CE-ADE5-00AA0044773D}
  </code></p>
  
  <p>[EDIT#2]: From my further research it looks like this is the defined and accepted behavior of the new Native Client. See <a href="https://msdn.microsoft.com/en-us/library/bb964722(v=sql.110).aspx" rel="nofollow">Stricter SQL_C_TYPE _TIMESTAMP and DBTYPE_DBTIMESTAMP parameter validation</a> for reference. But Microsoft can't take this change serious without any documentation how to properly use datetime2 then. Do they really require the developers themselves to round the DBTIMESTAMP before inserting them into the database? In some cases rounding even does not work because of some floating point precision errors. Take the example timestamp from above and you'll see the typical .9999 precision error. How is anybody supposed to get this strange behavior working? With this error messages you can't even save a current timestamp to the database without using the SQL function for <code>now()</code> because you'll usually run in those overflows. </p>