OdbcCommandBuilder fails on certain DateTime values with new {ODBC Driver 17 for SQL Server} driver

Mark Holt Trapeze 1 Reputation point
2021-01-08T15:12:37.283+00:00

I have to use the new {ODBC Driver 17 for SQL Server} driver because it supports TLS 1.2 (previously using the {SQL Server} driver. I have a table where the schema can change so I use the OdbcCommandBuilder class to generate the Update statement for me. It's been working great for 5+ years but using the new driver can sometimes generate a Concurrency Exception.
Using the Sql Profiler I narrowed it down to DateTime fields. If you do a select on a DateTime field then you might see, "2021-01-07 19:55:05.207". If you cast that to a DateTime2 then you will see more detail such as "2021-01-07 19:55:05.2070000" or "2021-01-07 19:55:05.2066667". If the detailed version ends in 4 zeros then the Update will succeed. If not then it will give the Concurrency Exception.
I don't see any settings in the OdbcCommandBuilder to affect this. Are there any settings on the database that might affect this? Or is this a bug?

.NET
.NET
Microsoft Technologies based on the .NET software framework.
3,415 questions
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,809 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-01-08T17:01:05+00:00

    Your problem is actually an EF problem, not a SQL Server problem.

    Please see:
    https://stackoverflow.com/questions/9382452/issues-with-entity-framework-concurrency-token-datatime-type

    PS. You should really use a RowVerison field, rather than a datetime for concurrency.

    0 comments
  2. Erland Sommarskog 101.8K Reputation points MVP
    2021-01-08T22:50:51.067+00:00

    This is a intentional change introduced in SQL 2016 and discussed in more detail here: https://learn.microsoft.com/en-us/troubleshoot/sql/database-design/sql-server-azure-sql-database-improvements.

    If you can change to datetime2(3), I think that is the best solution, since this type has an accuracy of 1 ms, and datetime only has an accuracy of 3.33 ms.

    0 comments