We are currently migrating our database from datetimeto datetime2 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.
In general we have OLE DB Consumers with "COLUMN_ENTRY*" accessors for our CRUD operations in place. For the datetime2 columns the members have the type DBTIMESTAMP. With the SQLNCLI provider the fraction part of the DBTIMESTAMP was silently truncated to the supported value. With the SQLNCLI11 an insert with too precise fraction results in this error:
DB_E_ERRORSOCCURRED Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.
According to this link, 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 this link, 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:
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()
};
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 COLUMN_ENTRY_PS with various values, but the inserts always failed.
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?
This is the test setup:
With the SQLNCLI provider the same code works.
[EDIT#1]: I dumped some more error information using AtlTraceErrorRecords and this confirmed the same error as in the linked Microsoft Connect report:
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}
[EDIT#2]: From my further research it looks like this is the defined and accepted behavior of the new Native Client. See Stricter SQL_C_TYPE _TIMESTAMP and DBTYPE_DBTIMESTAMP parameter validation 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 now() because you'll usually run in those overflows.
I did few tests and completely rewrote my answer.
I use SQL Server 2008 with Native Client 10. It already supports datetime2(7), so my tests are applicable.
datetime
I have a stored procedure that has a table-valued parameter. One of the columns of the parameter's table has datetime type. I wasn't using non-zero fractions of a second before, but tried now. It took me a while to figure out what is needed.
In order to use non-zero value in the DBTIMESTAMP.fraction field I had to configure both column description and parameter description: set DBCOLUMNDESC.bScale to 3 and DBBINDING.bScale to 3. Both bPrecision was left at 0. This MSDN doc helped me to realise that I have to set DBCOLUMNDESC.bScale as well.
The SQL Server Native Client OLE DB provider inspects the DBCOLUMDESC bScale member to determine the fractional seconds precision.
Once Scale is set to 3 I could set the value of DBTIMESTAMP.fraction to 555000000 and it was inserted successfully into the database as .557, i.e. the server rounded the value further to 1/3 of the millisecond (precision of the datetime type). But, when I tried to set the value of DBTIMESTAMP.fraction to 552100000, I got the same error as you are getting. So, the server expects the programmers to round the values themselves.
One simple way to truncate fractions would be something like this:
DBTIMESTAMP dt;
// set to any value from 0 to 999,999,999 (billionth of a second)
dt.fraction = 555123456;
// truncate fractions to milliseconds before inserting into the database
dt.fraction /= 1000000;
dt.fraction *= 1000000;
datetime2(7)
I changed the type of the column in the table-valued parameter to datetime2(7) and did few more tests.
I set Scale to 7 and could successfully insert into the database DBTIMESTAMP.fraction with the value 555123400, but when I tried to insert value 555123478 I got the same error. So, for datetime2(7) server also expects programmers to round the values themselves.
// truncate fractions to 100 nanoseconds precision
dt.fraction /= 100;
dt.fraction *= 100;
Should be enough.