Oracle Database Discussions

Hi,

I recently setup the oracle 11g gateway on a win2003 server and had success using it to connect to a mysql database. I am running into problems though connecting to ms sql servers.

Detailed setup:

installed dg4odbc on a win2003 box
listner.ora on win2003 box:
LISTENER =
(ADDRESS_LIST=
(ADDRESS=(PROTOCOL=tcp)(HOST=oragw.mydomain.com)(PORT=1521))
)

SID_LIST_LISTENER=
(SID_LIST=
(SID_DESC=
(SID_NAME=SQL2000)
(ORACLE_HOME=c:\oracle\product\11.1.0\tg_1)
(PROGRAM=dg4odbc)
)
(SID_DESC=
(SID_NAME=SQL2008)
(ORACLE_HOME=c:\oracle\product\11.1.0\tg_1)
(PROGRAM=dg4odbc)
)
(SID_DESC=
(SID_NAME=MYSQL)
(ORACLE_HOME=c:\oracle\product\11.1.0\tg_1)
(PROGRAM=dg4odbc)
)
)

configured a file called initSQL2000.ora:

# This is a sample agent init file that contains the HS parameters that are
# needed for the Database Gateway for ODBC

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = sql2000


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>


configured the odbc(driver version is 2000.86.3959.00) for SQL Server as follows:
name: SQL2000
description: SQL2000
server: SQL01
With Windows NT authentication using the network login ID
connect to SQL Server to obtain default setting for the additional configuration options
Use ANSI quoted identifiers
Use ANSI nulls, padding and warnings
Perform translation for character data

I then changed who the gateway server was running as to a domain account called gateway. I then granted the gateway user access to the appropriate databases(verified this by logging in to the databases as the new domain gateway user).

I then created the DB link from our oracle server(10gR2 under RHEL) using the following:
tnsnames.ora:
SQL2000=
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS =
(PROTOCOL = TCP)
(HOST = oragw.llewellyn.com)
(PORT = 1521)
)
)
(CONNECT_DATA =
(SID=SQL2000)
)
(HS=OK)
)

CREATE PUBLIC DATABASE LINK SQL2000 CONNECT TO "gateway" IDENTIFIED BY "gateway_password" USING 'SQL2000';

I then tested basic functionality:
select * from dual@SQL2000;
works fine
SELECT * FROM all_objects@SQL2000 returns zero rows though.
Trying to select from any actual tables results in
SQL Error: ORA-00942: table or view does not exist
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'FSSD'.[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Any suggestions?