none
datetime2 returned by DbConnection.GetSchema("DataTypes") for SQL Server 2005 RRS feed

  • Question

  •  

    Hi,

     

    When retrieving the data types with DbConnection.GetSchema("DataTypes") from an SQL Server 2005 (tested with "full" version and express), I get inconsistent results:

    - With SqlClient provider, the method returns the datetime2 data type,

    - But OleDb provider does not.

    Why is the datetime2 type returned at all? If I understand correctly, datetime2 is new in SQL 2008. I'm testing with 2005.

     

    If you wonder what I'm trying to do: I need to have a generic solution to create a database schema. At the minimum, it must work for SQL Server 2000 and later, and Oracle 9i and later. Jet (Access) and more would be nice.

     

    Thanks,

     

    Matthias

    Monday, April 14, 2008 9:13 AM

Answers

  • I mean the datetime2 type new in SQL Srv 2008. I already got some answers here:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3179967&SiteID=1

    (sorry for cross posting).

    SQL Server 2005 doesn't have datetime2, so it shouldn't be returned by GetSchema. OleDbConnection does it right, but not SqlConnection.

     

    Thanks!

     

    Monday, April 21, 2008 3:41 PM
  • That is because SQL Server 2008 is the first time ANSI SQL Time interval is implemented in SQL Server but it was implemented by Oracle and PostgreSQL for years now so those RDBMS provide support in their ADO.NET providers.

     

    Monday, April 21, 2008 3:50 PM

All replies

  • (At the minimum, it must work for SQL Server 2000 and later, and Oracle 9i and later. Jet (Access) and more would be nice.)

     

    By DateTime2 I am assuming you mean ANSI SQL Time Interval implementations it is not practical to create a generic version that will run in both RDBMS and Access.   That class implementation by Oracle in ODP.NET is not the same as Microsoft implementation for the Microsoft Oracle provider.  What you are getting from OLE DB is correct because both OLE DB and ODBC connections are very generic so some methods and classes with vendor specific implementations are ommited.  The solution is to use Enterprise Library so you can connect to both Oracle and SQL Server with the same classes and methods in ADO.NET.  Post again if you still have questions I can answer for both Oracle and SQL Server.  One more thing the only difference between SQL Server Express and for pay edition is features and capacity.

    Monday, April 21, 2008 2:05 PM
  • I mean the datetime2 type new in SQL Srv 2008. I already got some answers here:

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3179967&SiteID=1

    (sorry for cross posting).

    SQL Server 2005 doesn't have datetime2, so it shouldn't be returned by GetSchema. OleDbConnection does it right, but not SqlConnection.

     

    Thanks!

     

    Monday, April 21, 2008 3:41 PM
  • That is because SQL Server 2008 is the first time ANSI SQL Time interval is implemented in SQL Server but it was implemented by Oracle and PostgreSQL for years now so those RDBMS provide support in their ADO.NET providers.

     

    Monday, April 21, 2008 3:50 PM
  • OK, but why does GetSchema for SQL 2005 also show datetime2? Creating a table with datetime2 fails. And: This behaviour is new; a few months ago there was no datetime2 in the type information for 2005.

     

     

    Monday, April 21, 2008 3:55 PM
  • That maybe a bug because the first link is for SQL Server 2005 which does not include the DateTime2 but the second link is for SQL Server 2008 and it includes the DateTime2 data type. 

     

    http://msdn2.microsoft.com/en-us/library/ms131092.aspx


    http://msdn2.microsoft.com/en-us/library/ms131092(SQL.100).aspx

     

    Monday, April 21, 2008 4:15 PM
  • Yes, I'm pretty sure its a bug.

    Thanks for you assistance.

     

    Monday, April 21, 2008 4:19 PM