Linking sqlserver to oracle
How do I create a link to oracle 9i form sqlserver
I can tnsping oracle, I can connect via sqlplus, I can connect in odbc admin, I can connect via a udl, I can connect via ssis but I get ORA-12154: TNS:could not resolve the connect identifier specified when I try to set up a link in the management studio
Software/Hardware used:
Software/Hardware used:
ASKED:
October 28, 2008 6:12 PM
UPDATED:
October 31, 2008 3:26 PM
Answer Wiki:
One thing to check:
Verify the service name you are using to create the link in management studio. It needs to be exactly the same as defined in your tnsnames.ora file.
To see all answers submitted to the Answer Wiki: View Answer History.
Also check that your sqlnet.ora file does NOT have the parameter:
SQLNET.AUTHENTICATION_SERVICES=(NTS)
if it does, comment that line out.
The problem was that the provider needs to be set to ‘allow inprocess’
This is just the reverse of what the MS web site claims.
My problem now is that a query such as:
SELECT * FROM oraprod..SA.ENTITIES WHERE PID = 9009460
Where oraprod is the linked server and pid is defined as numeric(10,0) in oracle, returns this error:
Msg 9803, Level 16, State 1, Line 1
Invalid data for type “numeric”.
How do I get the number to match what is expected by oracle?
Have you tried to run the same query by using the T-SQL OPENQUERY funcion ?
(i.e. select * from OPENQUERY(oraprod,’select * from sa.entities where pid = 9009460))
does it give you the same error ?
Yep – the problem was resolved by upgrading the Oracle client on the SqlServer machine.
It now maps Oracle numeric to sqlserver decimal as it should