DBLINK from Oracle to SQLserver

From: Storey, Robert (DCSO) <"Storey,>
Date: Tue, 1 May 2012 15:01:32 +0000
Message-ID: <33B09BA255BD7142B217C2C0A0D0183406837A_at_DCSOSVMS02.dcso.org>

Morning folks.
This shouldn't be difficult, but as usual, it is. Creating a link between an Oracle DB and a Sql server DB.

1. I setup the System DSN and tested it. Works
2. Modified the TNSNAMES.ORA file - check
3. Modified the Listener file and reloaded - check
4. TNSping of the TNSnames entry successful - check
5. Created the inithsodbc.ora file - check
6. created a dblink putting the username/password in "" and the odbc service in ' ' - check
7. link created successfully - check
8. select * from table_at_linkname -ERROR Got the error back ORA-28500: connection from ORACLE to a non-Oracle system returned this message: [Generic Connectivity Using ODBC][Microsoft][ODBC SQL Server Driver][SQL Server]Cannot open database "test" requested by the login. The login failed. (SQL State: 37000; SQL Code: 4060) ORA-02063: preceding 2 lines from BOBLINK

Dropped the link and recreated it using just a junk username and password. Got the same error. Metalink has gone to sleep on me.

I'm missing something simple here but not sure what it is. Do I have to use a username with DBA privs on the sql server?

Thanks
Bob

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 01 2012 - 10:01:32 CDT