November 8, 2013 at 9:33 am
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!
November 8, 2013 at 11:06 am
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
----------------------------------------------------------------------------
November 8, 2013 at 2:25 pm
Thanks - I'll try to uninstall everything and start over with 32 bit client and start from there.
November 11, 2013 at 7:49 am
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?
November 11, 2013 at 10:10 am
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
----------------------------------------------------------------------------
November 11, 2013 at 11:02 am
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?
November 11, 2013 at 4:42 pm
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
----------------------------------------------------------------------------
November 12, 2013 at 7:25 am
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!
November 14, 2013 at 10:48 am
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.
November 14, 2013 at 2:15 pm
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
----------------------------------------------------------------------------
November 14, 2013 at 2:22 pm
Are you sure, Named Pipes, Shared Memory and TCP/IP protocols are enabled on SQL Server and it is set to run in Mixed Mode Authentication?
November 15, 2013 at 8:16 am
yep, and in order, 1) Shared Memory, 2) TCP/IP, 3) Named Pipes, 4) VIA (disabled). Mixed mode - yep.
In running the scripts to create the linked server, the provider string ends up with no value - is that OK?
November 15, 2013 at 10:17 am
You definitely need to give it a provider. We're you able to modify that user name?
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
November 15, 2013 at 10:54 am
yes, I tried the username@servicename in the username field with no luck. I tried creating the Linked Server via script from your blogs as well as from SSMS - create new linked server with several variations but made no forward progress.
I'm wondering if there needs to be something specific on the Oracle side in the tnsnames or listener file that is missing for the linked server to work. If that could possibly be a problem, I can add them here, if it would help troubleshoot .
November 15, 2013 at 12:14 pm
Your linked server is working, it's getting connected to Oracle without having to specify 'username@service' that's the problem. Were you able to search the Oracle forums to find an answer to that question? It's has to be something very basic, I just can't recall it right now.
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply