Linking sqlserver to oracle

65 pts.
Tags:
Linking to Oracle from SQL Server
Oracle 9i
Oracle SQL
SQL Server
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Randym
    Also check that your sqlnet.ora file does NOT have the parameter: SQLNET.AUTHENTICATION_SERVICES=(NTS) if it does, comment that line out.
    1,740 pointsBadges:
    report
  • Wgself
    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?
    65 pointsBadges:
    report
  • carlosdl
    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 ?
    68,525 pointsBadges:
    report
  • Wgself
    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
    65 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following