How to migrate datetime to datetime2 using the new SQL Server Native Client
We are currently migrating our database from datetime
to datetime2
, including SQL Server Native Client v11 (SQL Server 2012). The database upgrade was easy to do, but problems arise with the new SQL Server Native Client 11 we want to use.
In general, we have OLE DB users using "COLUMN_ENTRY *" for our CRUD operations. For columns, datetime2
members are of type DBTIMESTAMP
. With the provider, the SQLNCLI
fractional part has DBTIMESTAMP
been quietly truncated to a supported value. Using SQLNCLI11
an overly accurate fraction insertion 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 inserted into the field. From this I assumed that the fractional part of the DBDATETIME element was too precise to be inserted. According to this link , newer Native Client Versions (10 and 11) are not truncated, but fail on error. Take this simplified example of 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()
};
In some part of the code, I initialize the timestamp to 2015-08-10 07: 47: 49.986149999 and no insert is done. If I reset the share to 0, the insert works; any value near 0 is not executed. I tried to provide the precision and scale of datetime using COLUMN_ENTRY_PS
with different values, but the inserts always fail.
How do I get the root client to just accept the value and truncate it? It is quite obvious that we cannot trim all values manually to the supported DB precision. I couldn't find the correct documentation on how to use datetime2 with the new Native Client. Are there any conversions or settings missing to handle the datetime2 data correctly?
This is a test setup:
- Windows 7 64bit
- SQL Server Native Client 11 (SQLNCLI11)
- SQL Server 2012
- DBPROP_INIT_LCID = 2055
- DBPROP_INIT_PROMPT = 4
- DBPROP_INIT_DATASOURCE = local
- DBPROP_AUTH_INTEGRATED = SSPI
The SQLNCLI
same code works with the provider .
[EDIT # 1]: I was dropping a few errors with 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 Validation of SQL_C_TYPE _TIMESTAMP and DBTYPE_DBTIMESTAMP Parameters for reference. But Microsoft cannot take this change seriously without any documentation on how to use datetime2 correctly. Do they themselves need developers to bypass DBTIMESTAMP before inserting them into the database? In some cases, rounding doesn't even work due to some floating point precision errors. Take the timestamp example from above and you will see the typical .9999 precision error. How is someone supposed to make this strange behavior work? With these error messages, you can't even store the current timestamp in the database without using the SQL function tonow()
because you usually run these overflows.
source to share
I did some tests and completely rewrote my answer.
I'm using SQL Server 2008 with my own client 10. It already supports datetime2(7)
, so my tests are applicable.
date and time
I have a stored procedure with a table-value parameter. One of the columns of the design table is of type datetime
. I didn't use non-zero fractions of a second before, but now I tried. It took me a while to figure out what was needed.
To use a non-zero value in the field DBTIMESTAMP.fraction
, I had to adjust the column description and parameter description: set DBCOLUMNDESC.bScale
to 3 and DBBINDING.bScale
to 3. Both bPrecision
remained at 0. This MSDN document helped me figure out what I needed to set as well DBCOLUMNDESC.bScale
.
The SQL Server Native Client OLE DB Provider checks DBCOLUMDESC bScale to determine the precision of fractional seconds.
Once Scale
set to 3, I can set the value DBTIMESTAMP.fraction
to 555000000
and was successfully inserted into the database as .557
, i.e., the server has rounded the value down to 1/3 millisecond (type precision datetime
). But , when I tried to set the value DBTIMESTAMP.fraction
to 552100000
, I got the same error as you. Thus, the server expects 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 column type in the table-value parameter before datetime2(7)
and did some more tests.
I set Scale
to 7 and was able to successfully insert into the database DBTIMESTAMP.fraction
with the value 555123400
, but when I tried to insert the value 555123478
, I got the same error. So the 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.
source to share