none
Problem accessing TIME(7) column in SQL Server 2008 using OdbcDataAdapter RRS feed

  • Question

  • Hi,

    I'm getting an ArgumentException "Unknown SQL type - SS_TIME_EX." when I try to select TIME(7) columns using the OdbcDataAdapter on SQL Server 2008.

    This is on a test database where I am simulating a third-party non-SQL server ODBC database. (So I can't use SqlClient).

    Exception details below.

    Please help!

    -David St

     

     

    System.ArgumentException was caught
      Message=Unknown SQL type - SS_TIME_EX.
      Source=System.Data
      StackTrace:
           at System.Data.Odbc.TypeMap.FromSqlType(SQL_TYPE sqltype)
           at System.Data.Odbc.OdbcDataReader.GetSqlType(Int32 i)
           at System.Data.Odbc.OdbcDataReader.GetFieldType(Int32 i)
           at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetFieldType(Int32 ordinal)
           at System.Data.ProviderBase.SchemaMapping.SetupSchemaWithoutKeyInfo(MissingMappingAction mappingAction, MissingSchemaAction schemaAction, Boolean gettingData, DataColumn parentChapterColumn, Object chapterValue)
           at System.Data.ProviderBase.SchemaMapping..ctor(DataAdapter adapter, DataSet dataset, DataTable datatable, DataReaderContainer dataReader, Boolean keyInfo, SchemaType schemaType, String sourceTableName, Boolean gettingData, DataColumn parentChapterColumn, Object parentChapterValue)
           at System.Data.Common.DataAdapter.FillMappingInternal(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
           at System.Data.Common.DataAdapter.FillMapping(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 schemaCount, DataColumn parentChapterColumn, Object parentChapterValue)
           at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue)
           at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
           at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)
           at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
           at frmMain.TestConnection_ODBC(Boolean singleRecord) in C:\EDS\DEV2\Developer\DatabaseTest\DatabaseTest\frmMain.vb:line 93
      InnerException:


    David Streeter
    Friday, March 18, 2011 6:20 AM

Answers

  • Hi David,

    Based on the MSDN document, http://msdn.microsoft.com/en-us/library/bb677302.aspx, when converting the TIME(7) value back to ODBC data type SQL_C_TIME, we will receive error which is similiar with "Fails – invalid time literal".  "Unknown SQL type - SS_TIME_EX" can be a wrapper of the error.  

    Could you convert the time value to VARCHAR instead?

    ==================================================================
                using (OdbcConnection conn = new OdbcConnection(@"Driver={SQL Server Native Client 10.0};Server=MyDBServer;Database=TestDB;Trusted_Connection=yes;"))
                {
                    conn.Open();
                    using (OdbcCommand cmd = new OdbcCommand("SELECT ID, Name, CAST(TimeValue as VARCHAR) as TimeValue FROM TimeTest", conn))
                    {
                        OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                    }
                }
    ==================================================================

    Also, you want to use a 3rd party database, the behavior may be different again because it will be determined by the detailed data provider(driver) you are using.  

    Have a nice weekend!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Friday, March 18, 2011 9:19 AM
    Moderator

All replies

  • Hi David,

    Based on the MSDN document, http://msdn.microsoft.com/en-us/library/bb677302.aspx, when converting the TIME(7) value back to ODBC data type SQL_C_TIME, we will receive error which is similiar with "Fails – invalid time literal".  "Unknown SQL type - SS_TIME_EX" can be a wrapper of the error.  

    Could you convert the time value to VARCHAR instead?

    ==================================================================
                using (OdbcConnection conn = new OdbcConnection(@"Driver={SQL Server Native Client 10.0};Server=MyDBServer;Database=TestDB;Trusted_Connection=yes;"))
                {
                    conn.Open();
                    using (OdbcCommand cmd = new OdbcCommand("SELECT ID, Name, CAST(TimeValue as VARCHAR) as TimeValue FROM TimeTest", conn))
                    {
                        OdbcDataAdapter da = new OdbcDataAdapter(cmd);
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                    }
                }
    ==================================================================

    Also, you want to use a 3rd party database, the behavior may be different again because it will be determined by the detailed data provider(driver) you are using.  

    Have a nice weekend!

    Thanks

     


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Friday, March 18, 2011 9:19 AM
    Moderator
  • Hi David,

    Could you please let us know how is the problem now?

    If you need any further assistance, please feel free to let me know.

    Good day!

    Thanks


    Michael Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Tuesday, March 22, 2011 1:27 AM
    Moderator