SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Totally lost on creating a linked server from 2008R2 to Oracle


Totally lost on creating a linked server from 2008R2 to Oracle

Author
Message
david.robson
david.robson
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 194
I've reviewed the two threads here, and many articles from doing a Goggle search. They all follow a theme, but aren't exactly the same, and aren't all that clear to me.
So in trying to build my linked server on my SQL Server box, what to I need to install as far as Oracle goes? 32 bit client, 64 bit client, or both? Anything else?

Do I need to be aware of anything if I install both - like overwriting files? Should I install them to the same of different folders? Where does TNSNames come into play? does it get created during the client install? Do I need to create one, and if so where? How do I test it?

I've installed both clients and after searching my machine, I don't have a TNSPING utility (as I've seen in some of the articles for testing) - should I? Is this something I need to download? I'm totally lost. The only thing I see on my machine after the install of both clients is a ...11g_home1 entry under programs. The application Development folder only contains an entry for Oracle ODBC help and the Configuration and Migration Tools folder only contain an entry for Microsoft ODBC administrator. It seems like I should have more entries. ??? Thanks for any help you can offer!
SQLDCH
SQLDCH
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3335 Visits: 3401
IIRC, the Oracle client install should have installed the Oracle command line utility 'sqlplus'. If the location of 'sqlplus' was entered into your %PATH% variable, you can open a DOS prompt and type 'sqlplus' without getting an error. Use it to connect to your Oracle database. This is an alternate way of testing your TNS names connectivity (other than TNSPing).

I wrote a blog post on how to create a linked server to Oracle using T-SQL, it may help. http://expressnetsolutions.com/sqldch/?p=89. Unfortunately, I glossed over the Oracle install and troubleshooting...but if you follow the steps in the T-SQL you may get it to work.

I've seen cases where both the 32 and 64 bit client needed installing. Install the 32 bit version, attempt to create your linked server...if it doesn't work go with the 64 bit version.

It was a few years ago so working with the Oracle TNS names is fuzzy. IIRC there should be a GUI based tool in your Start menu to help you create your TNSNames file.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
david.robson
david.robson
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 194
Thanks - I'll try to uninstall everything and start over with 32 bit client and start from there.
david.robson
david.robson
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 194
So I can't find any uninstall options anywhere. Can I just delete the registry keys, Oracle (home) folders created during the install and start over with the 32 bit client install?
SQLDCH
SQLDCH
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3335 Visits: 3401
Try running with what you have. Open up SSMS, connect to your instance and go to Server Objects > Linked Servers > Providers. Follow the steps from the blog post and see if you can get connected with one of the Oracle drivers from that list.

There's info on the web on how to do a complete un-install of the Oracle client, I'm pretty sure it's actually buried in the layers of the Oracle documentation (yeah, Oracle doc navigation is rough). Try this one: http://docs.oracle.com/html/B10131_02/deinstall.htm#i1008427

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
david.robson
david.robson
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 194
I reinstalled ODAC and see the Oracle OLE DB provider mention in the blog article, but where and how do I set up TNSNames? Or is that (the datasource entry) referring to the TNSNames on the Oracle box?
SQLDCH
SQLDCH
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3335 Visits: 3401
I looked in the Oracle doc, you'll want to open and use the Oracle Net Configuration Assistant. It's the GUI that'll create and update your TNSNames.ora file:

http://docs.oracle.com/cd/B19306_01/install.102/b15684/post_install.htm#CHDBIHEE

Once you establish that connection, use that TNS name in as the @datasrc value from the blog.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
david.robson
david.robson
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 194
Thanks! I figured something wasn't quite right with my client install - I don't have a net configuration entry! That will be my task to resolve for today!
david.robson
david.robson
SSC Journeyman
SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)SSC Journeyman (93 reputation)

Group: General Forum Members
Points: 93 Visits: 194
OK, so I think some good progress has been made.
I am now successful with TNSPing. I've added the linked server. Now when testing the linked server, I get the following error:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (Microsoft SQL server, Error:233)

I AM able to launch SQL*Plus from the command prompt and run a select statement.. To do this, I'm connecting using the command:
c:\SQLPlus <user>@<servicename> where <user> and <servicename> are replaced with the correct values.

If I launch SQL*Plus from Windows, (Start>all programs>OracleHome>Application Development>SQL Plus) I am immediately prompted for the username then password, and that connection fails.

When prompted for username - if I enter <user>@<servicename>, and then the password at the password prompt, it connects.

This is progress, but I'm not sure what this is telling me.
SQLDCH
SQLDCH
Hall of Fame
Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)Hall of Fame (3.3K reputation)

Group: General Forum Members
Points: 3335 Visits: 3401
I vaguely recall running into something like that...the Oracle forums are probably your friend, now.

In your linked server set up, try setting the username as '<user>@<servicename>' and see what happens. You can right click your linked server, select 'Properties' then go to the 'Security' options page. If you recreate the linked server, set @rmtuser to the aforementioned username.

----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search