I need to configure linked servers for a large number of MS SQL, Oracle, Sybase and Ingres servers. I would prefer not to have to use open row set/ open query for coding/performance reasons.
The central server has W2K/SQL Server 2000 as well as Oracle 9I, Sybase 12.5 and Ingres 2.6 clients (ODBC 3.5)
Connectivity through the clients tests out ok. I have configured linked servers for all database flavours. I can see the catalogues of the remote databases clearly through Enterprise Manager. However, when I use the four part naming convention to query databases, this only works for SQL Server and Oracle but not Sybase or Ingres.

Sybase:
Sybase Interfaces file has details of remote server and port number.

Linked Server Properties
General Tab
Product name: Sybase
Provider String: DRIVER=Sybase ASE ODBC Driver;NLN=Winsock;NA=serveridA,5000
Catalog: master


Security Tab
Be made using this security context: sa, password xyz

The query:
select * from sybasetest.master.dbo.sysindexes

gives the following error....

Invalid use of schema and/or catalog for OLE DB provider 'MSDASQL'. A four-part name was supplied, but the provider does not expose the necessary interfaces to use a catalog and/or schema.
OLE DB error trace [Non-interface error].

Ingres
Created a global vnode “vnodetest” with login ingres (sa equiv). Tested successfully.

Created a System DSN, using Ingres ODBC Administrator. In this dialog, datasource given name jctest. Under server details, vnode name set to “vnodetest”, type “ingres”, database “iidbdb”.


Linked Server Properties
General Tab
Product name: Ingres
DataSource: jctest
Catalog: iidbdb

Security Tab
Be made using this security context: ingres , password xyz


The query:
select * from ingres_test.iidbdb.ingres.iitables

gives the following error....

Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'ingres_test' does not contain table 'iidbdb.ingres.iitables'. The table either does not exist or the current user does not have permissions on that table.
OLE DB error trace [Non-interface error: OLE DB provider does not contain the table: ProviderName='ingres_test', TableName='iidbdb.ingres.iitables'].


Any help is much appreciated,
John