Problem: Data in ArcView SQL tables becomes corrupt or disappears upon reopening a project
Solution or Workaround
When reopening an ArcView project with SQL tables, some of the fields in the SQL table either have no values, contain values that belong to other fields, or the SQL table itself is missing from the project. An ODBC error message may also get written to the ODBC.errlog file. The message contains errors like:
"Error in assignment on column number 1"
"Invalid column number"
"Call to SQLGetData received error"
"Call to SQLExecute received error"
There are two conditions which together can cause this issue:
- When the SQL table is brought into ArcView, the user chooses All Columns (for example '*') from the Columns list in the SQL Connect dialog box.
- After the project is saved and closed, the table is edited within the DBMS (Database Management System, i.e. MS Access, Oracle) such that the order of existing fields (from left to right) is altered. This can be done either by adding new fields to any position other than the far right or by rearranging the order of the existing fields.
When adding the SQL tables to ArcView, select the columns in the table individually rather than selecting all columns. If the SQL query has already been made, delete the table from the project after reopening it and then add the SQL table back into the project. This will reset the SQL table so that it reflects the current state of the database table.