Ingres Has Its Limits

Unfortunately we run an Ingres database for our internal systems.
Fortunately our web stuff uses SQLServer 2000 for its backend so we have a workaround for one of Ingres' major limitations(You know there is a problem when you are praising the quality of a Microsoft product). Ingres does not allow cross catalog joins, you can set up a distributed database but that requires an insane amount of work and repeated definitions.

The solution:

• Set up Ingres ODBC on the Windows 2K server.


• Make sure it auto starts etc.


• Add an ODBC entry for each Ingres database you wish to access.


• Go into SQLServer Enterprise manager.


• Add a remote server for each ODBC entry with appropriate security access.

The sql

  select *

     from RemoteServerName1..schema.tablename1 as a

          inner join RemoteServerName2..schema.tablename2 as b

             on a.pkfield = b.pkfield

now works.
We now have the ability to do cross catalog joins on an Ingres database server.