DBLINK from Oracle to SQLserver
Date: Tue, 1 May 2012 15:01:32 +0000
This shouldn't be difficult, but as usual, it is. Creating a link between an Oracle DB and a Sql server DB.
- I setup the System DSN and tested it. Works
- Modified the TNSNAMES.ORA file - check
- Modified the Listener file and reloaded - check
- TNSping of the TNSnames entry successful - check
- Created the inithsodbc.ora file - check
- created a dblink putting the username/password in "" and the odbc service in ' ' - check
- link created successfully - check
- 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?