Notifications

1490 views

Invalid Table or Database View Causes Schema Error in ODBC Driver

Problem
An invalid table or database view causes schema errors when using the ODBC driver with instances prior to the Geneva release. This issue produces the same symptoms as described in KB0595630: Resolving ODBC Driver schema, table, or column not found errors.
 
Symptoms
The ODBC Driver returns one of the following errors in an instance prior to the Geneva release:
  • ERROR [HY000] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Exception retrieving tables schema: java.net.SocketTimeoutException: Read timed out[1020]
  • ERROR [42S02] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Base table:<a_table> not found.[10129]
  • ERROR [HY000] [SN][ODBC ServiceNow driver][OpenAccess SDK SQL Engine]Cannot create schema.Cannot retrieve a DB schema. Please run <instance>?SCHEMA in your browser and try again. Also make sure that the table descriptor cache can hold all your tables and DB views. You can check the table descriptor stats runing <instance>/xmlstats.do in your browser.[1050]
  • ERROR [HY000] [DataDirect][ODBC OpenAccess SDK driver][OpenAccess SDK SQL Engine]Could not find any column information for table:<a_table>.[10131]
  • Any other error that states the schema, table, or column was not found 
Cause

The instance contains an invalid table or an invalid database view and when ODBC calls <table>?SCHEMA, it fails.

 

Resolution
First, evaluate if the error is caused by an invalid table or an invalid database view.
  1. Navigate to <instance>.service-now.com?SCHEMA.
    • If you do not receive valid XML, there is an issue with a table or database view
    • If the XML is valid, review KB0595630 as that error produces the same symptoms
    • If you receive invalid XML, you must find out which table or database view is causing the error
  2. Modify the SchemaProcessor script include "writeTables" function.
    Change the following:

        while (gr.next()){
          var tableName = gr.getValue('name');
          if ( JSUtil.nil(tableName))
              continue;
    

    Add two debugging lines (with //debugging) as follows: 

        var index = 0; // debugging 
        while (gr.next()){
          var tableName = gr.getValue('name');
          if ( JSUtil.nil(tableName))
              continue;          
          gs.log('Processing table ' + index++ + ' with name=' + tableName); // debugging 
  3. Navigate to <instance>.service-now.com?SCHEMA again.
  4. Navigate to System Logs > Script Log Statements.
  5. Filter the list to include only Message values that contain 'Processing table' and sort by the Message field.
  6. Use the log statements to identify the table that was logged last. This is the table causing the issue.
  7. Modify the SchemaProcessor script include to exclude any invalid tables or database views.
  8. Repeat steps 2 - 6 until <instance>.service-now.com?SCHEMA returns a valid XML document.
  9. Remove any debugging statements from the SchemaProcessor script include.

Article Information

Last Updated:2016-10-28 06:50:20
Published:2016-10-28