Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Totally lost on creating a linked server from 2008R2 to Oracle Expand / Collapse
Author
Message
Posted Friday, November 08, 2013 9:33 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:33 AM
Points: 46, Visits: 132
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!
Post #1512729
Posted Friday, November 08, 2013 11:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:12 PM
Points: 1,194, Visits: 2,666
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.


----------------------------------------------------------------------------
My LinkedIn profile - LinkedIn
Sacramento SQL Server users group - http://sac.sqlpass.org
My blog - http://www.expressnetsolutions.com/sqldch
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Post #1512772
Posted Friday, November 08, 2013 2:25 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:33 AM
Points: 46, Visits: 132
Thanks - I'll try to uninstall everything and start over with 32 bit client and start from there.
Post #1512813
Posted Monday, November 11, 2013 7:49 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:33 AM
Points: 46, Visits: 132
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?
Post #1513140
Posted Monday, November 11, 2013 10:10 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:12 PM
Points: 1,194, Visits: 2,666
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


----------------------------------------------------------------------------
My LinkedIn profile - LinkedIn
Sacramento SQL Server users group - http://sac.sqlpass.org
My blog - http://www.expressnetsolutions.com/sqldch
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Post #1513195
Posted Monday, November 11, 2013 11:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:33 AM
Points: 46, Visits: 132
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?
Post #1513205
Posted Monday, November 11, 2013 4:42 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:12 PM
Points: 1,194, Visits: 2,666
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.


----------------------------------------------------------------------------
My LinkedIn profile - LinkedIn
Sacramento SQL Server users group - http://sac.sqlpass.org
My blog - http://www.expressnetsolutions.com/sqldch
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Post #1513300
Posted Tuesday, November 12, 2013 7:25 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:33 AM
Points: 46, Visits: 132
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!
Post #1513457
Posted Thursday, November 14, 2013 10:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 10, 2014 10:33 AM
Points: 46, Visits: 132
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.
Post #1514394
Posted Thursday, November 14, 2013 2:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 4:12 PM
Points: 1,194, Visits: 2,666
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.



----------------------------------------------------------------------------
My LinkedIn profile - LinkedIn
Sacramento SQL Server users group - http://sac.sqlpass.org
My blog - http://www.expressnetsolutions.com/sqldch
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------

Yeah, well...The Dude abides.
Post #1514481
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse