Troubleshooting "table not found" errors

Recently, one of our customers was experiencing problems when attempting to insert some Oracle data into a SQL Server table. The insert was failing because the target table in the SQL Server instance was not present in the database that the customer was connecting to.

Ultimately, the solution to this issue was the simplest one. This troubleshooter includes this solution and others, in an attempt to present a list of potential fixes for the problem in logical order. Although the troubleshooter is based around an Easysoft ODBC driver using SQL Server as its target database, many of the steps are applicable to other unixODBC-based drivers for other databases.

1. Check your data source (DSN) for your target database.

   This will usually be defined in /etc/odbc.ini.

   Important Don't bypass these steps just because your DSN is a copy from a working setup on another machine. Particularly if that working setup is on another platform or uses a different version of the driver. Different versions of an ODBC driver may parse the odbc.ini file differently, for example, some may use the last version of a DSN or DSN attribute they find when there are duplicates, some may use the last. Additionally, a different driver on a different platform may cease parsing the odbc.ini file if there is a problem character in the file such as a carriage return.

   • Check that there is only one copy of the data source. If there are multiple versions of the data source, either rename them or remove other versions. For example, you want this:

     [MYDSN]
     Database=MYDB
     

     —Or—

     [MYDSN1]
     Database=MYDB1
     
     [MYDSN2]
     Database=MYDB2
     

     Not

     [MYDSN]
     Database=MYDB
     
     [MYDSN]
     Database=MYDB
     

   • When you're sure that you have only one copy of the DSN, check that the DSN only has line specifying the target database. For example, you want this:

     [MYDSN]
     Database=MYDB
     Server=MYMACHINE
     .
     .
     .
     [ANOTHERDSN]
     

     Not

     [MYDSN]
     Database=MYDB
     Server=MYMACHINE
     Database=MYDB2
     .
     .
     .
     [ANOTHERDSN]
     

     —Or—

     [MYDSN]
     Database=MYDB
     Server=MYMACHINE
     Database=
     .
     .
     .
     [ANOTHERDSN]
     

2. If you do not explicitly specify a database, check with your DBA that the default database for your user is the one you think it is. For example, in SQL Server it's possible to configure a login to connect to a particular database, so in:

   [MYDSN]
   Database=MYDB
   Server=MYMACHINE
   User=MYUSER.
   .
   .
   [ANOTHERDSN]
   

   MYUSER might initially connect to say, AdventureWorks if the login has been configured to a particular database, or the Master database if it has not.

3. Check that you are connecting to the DSN that you think you are. Even if you have added your DSN to a pre-existing version of, say /etc/odbc.ini, it doesn't mean your Driver Manager is looking in this file. Depending on how the driver manager is built or the environment is set, it could be looking in a different location. To check, try commenting out the Driver attribute in the data source. If you can still connect, you are using a different version of the DSN. Use a program such as strace or truss to find out which odbc.ini file is being used. For example:

   $ more /etc/odbc.ini
   [MYDSN]
   #Driver=Easysoft ODBC-SQL Server
   $ /usr/local/easysoft/unixODBC/bin/isql.sh -v MYDSN
   SQL>
   $ strace -o -f /tmp/odbc.log /usr/local/easysoft/unixODBC/bin/isql.sh -v MYDSN
   $ grep odbc.ini /tmp/odbc.log

   If you have copied a DSN over from another machine, try repeating this process on that machine to verify the location of the source DSN.

4. Check that you are connecting to the DBMS you think you are. For example, if it's not too disruptive try pausing or stopping the target instance or service for the DBMS. If you can still connect, you are connecting to a DBMS on another machine. Perhaps your network has been configured such that another machine can appear to have the same IP address as the one specified in the DSN.
5. In isql, enter help. In the list of tables returned, what database name is shown? Is it the one you expect? If not, what happens if you enter:

   use database

   Replace database with the name of the target database. If you cannot change database, check with your DBA whether there is a logon trigger that controls access to databases by IP address. In SQL Server Management Studio, logon triggers are under INSTANCE > Server Objects > Triggers.