Creating a new Oracle Shared Datasource from Visual Studio 2005 client

globalwm2
globalwm2 used Ask the Experts™
on
I'm running Win7x64 with Oracle client. I can connect to the particular instance using TNSNAMES.ora fine from: Sqlplus and Toad. When I try to create a new DataSource in VS2005, I get the ORA-12154.

I tried the following:

1. Added WRITE security on ORACLE_HOME folder.
2. Added NAMES.DEFAULT_DOMAIN to SQLNET.ora
3. Created another TNSNAMES entry with FQDN as #2 above.

I also downloaded: ODTwithODAC1020221.exe but when I installed, I received the following:

Starting Oracle Universal Installer...

Checking installer requirements...

Checking operating system version: must be  4.0, 5.0, 5.1, 5.2 or 6.0 .    Actua
l 6.1
                                      Failed <<<<

Exiting Oracle Universal Installer, log for this session can be found at C:\Prog
ram Files (x86)\Oracle\Inventory\logs\installActions2011-05-17_12-00-48PM.log

Please press Enter to exit...

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
If you Google around there are a few threads that talk about how to install Oracle in windows 7 when it fails the OS check.  I'll let you find those.

Toad and sqlplus are like using a different ORACLE_HOME than what Visual Studio is using.  If you have more than one Oracle product installed you either need a tnsnames.ora file in each home or you can set a TNS_ADMIN environment variable to point to a common tnsnames.ora file.

Top Expert 2009

Commented:
Check your PATH, see which Oracle home / bin comes first. That is the one that VS is using. Then make sure a tnsnames.ora exists in that one, as Steve says.

IF you are running VS 2005, make sure you have a 32-bit Oracle client.

Author

Commented:
Interesting.

I found this:
http://forums.oracle.com/forums/thread.jspa?threadID=1133227

and edited my ORAPARAM file - added the '6.1' entry for my OS.

Reran installer (as Admin) and it launched.

Selected: Oracle DA Components (noting new Oracle Home info)

Unchecked "VS 2003"  (VS 2005 was checked)

After install, copied over TNSNAMES.ora and SQLNET.ora files to new "client_2" home.

Added: TNS_ADMIN env var pointed to original (client_1) home.

Restarted Windows 7

Launched VS2005, opened Project, browsed Report. Clicked the Data tab and received:

TITLE: Microsoft Report Designer
------------------------------

A connection cannot be made to the database.
Set and test the connection string.


------------------------------
ADDITIONAL INFORMATION:

ORA-12154: TNS:could not resolve the connect identifier specified
 (System.Data.OracleClient)

------------------------------
BUTTONS:

OK
------------------------------


Tried to Test the connection, after supplying the username/pass, clicked Test Connection and received the ORA-12154 TNS:could not resolve... error.

????
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
mrjoltcola:

Path now points to second home first - created above:
C:\oracle\product\10.2.0\client_2\bin;C:\oracle\product\10.2.0\client_1\bin;...

I copied TNSNAMES from client_1 to client_2 so we should be good there.

As far as the 32-bit version of SQLPLUS, how do I determine this?

(SQL*Plus: Release 10.2.0.1.0)

Top Expert 2009

Commented:
So you are back to my suggestion of checking your PATH and ensuring which Oracle home is first in the path.

Hop out into a DOS command window and use tnsping to test the connection:

Assuming your alias ORA1

tnsping ORA1

It still appears that the first Oracle home in your PATH doesn't have the alias in its tnsnames.ora (under %ORACLE_HOME%\network\admin)
Top Expert 2009

Commented:
Oops, we criss-crossed in the ether...

sqlplus banner should say 32 or 64 bit

Author

Commented:
even though the install completed successfully - there was this hicup:

<snip>
Key = HKEY_LOCAL_MACHINE

SubKey = SOFTWARE\Microsoft\.NETFramework

Value = sdkInstallRootv2.0
INFO: Query Returned: C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\
INFO: Setting variable 's_gacutilPath' to 'C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\'. Received the value from a code block.
INFO: Calling Action SpawnActions10.1.0.2.0  Spawn
      installcommand = C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\\devenv.exe /setup
      deinstallcommand = C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\\devenv.exe /setup
      WaitForCompletion = null

INFO: Calling Action SpawnActions10.1.0.2.0  Spawn
      installcommand = C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\\bin\gacutil.exe /I C:\oracle\product\10.2.0\client_2\odt\vs2005\Oracle.VsDevTools.dll
      deinstallcommand = C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\\bin\gacutil.exe /u Oracle.VsDevTools,Version=2.102.2.20
      WaitForCompletion = true

INFO: Exception occured during spawning :CreateProcess: C:\Program Files (x86)\Microsoft Visual Studio 8\SDK\v2.0\\bin\gacutil.exe /I C:\oracle\product\10.2.0\client_2\odt\vs2005\Oracle.VsDevTools.dll error=2
INFO: Exception thrown from action: Spawn
Exception Name: RuntimeException
Exception String: An error occurred during runtime.
Exception Severity: 0
INFO: Exception handling set to prompt user with options to  Retry  Ignore

<snip>

Author

Commented:
Going to try and copy "gacutil' into that directory - I don't have it...
Top Expert 2009

Commented:
So when you create the data source in VS2005, on the Choose Your Data Connection, you choose CHange Data Source and pick Oracle Database...

What then shows in the Data provider list. It should show all registered providers. Which did you choose?
Top Expert 2009

Commented:
If I were you, unless you specifically need a certain version of the client, or ODP.NET, I would deinstall all but one.

I personally do have 2 on my machine, one 32-bit and one 64-bit, but that is because I run 64-bit server, but develop with 32-bit tools, so I maintain my PATH accordingly, depending on what I am doing.

It helps to simplify, if possible.

Author

Commented:
I reran the Oracle Installer, it stopped on the gacutil pert. I asked a co-worker for theirs and dropped in that BIN directory and clicked RETRY and the installer completed with no errors.

Rebooting now...

Author

Commented:
mrjoltcola:

Under Data Provider, '.NET Framework Data Provider for Oracle' is selected.

Still get the error.

Author

Commented:
Sorry, missed this:

C:\Users\anthony.ballo>tnsping <my tnsnames alias>

TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 17-MAY-2
011 14:38:54

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
C:\oracle\product\10.2.0\client_2\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact <snip>

OK (0 msec)

Author

Commented:
The SQLPLUS banner when running: C:\SQLPLUS /nolog does not display 32 or 64...

Checked lib directory under $ORACLE_HOME.
For 32-bit should be $ORACLE_HOME/lib
for 64-bit should be $ORACLE_HOME/lib64

I have a LIB - not a LIB64

So 32bit SQLPLUS ?

Author

Commented:
From VS2005, when I clicked "Connect to a Oracle DB" - I would get this error:



The fix was:


I was able to get my version working. I'm not sure if this is the complete solution, but after this step VS started working with the Oracle Developer Tools.

You should have Oracle.DataAccess, Oracle.VsDevTools, and Oracle.Management.Omo in the Windows/Assembly folder. You can have different versions of this depending on if you had the beta or 9i installed; make sure you have version 2.102.2.20 of Oracle.DataAccess and Oracle.VsDevTools in the list.

If not then browse to:
C:\WINDOWS\Assembly

In a different window browse to:
C:\oracle\product\10.2.0\client_2\odp.net\bin\2.x\Oracle.DataAccess.dll

Click and drag the .dll to the Assembly folder.

Good Luck!

Message was edited by:
TorgerJW



Now the Add Connection brings up a window where I can browse all my TNSNAMES aliases.




Testing the connection still gives me an ORA-12154 error - but I'm getting closer.

Author

Commented:
Sorry, the error above was something like:

package 'oracle developer tools for visual studio .net' has failed to load...  do you want to disable the addin?'

Author

Commented:
So now I have Oracle Developer Tools for Visual Studio .NET   10.2.0.2.20 installed:

1. TNSNames file works and appears in drop-down menu in "Connect to an Oracle Database'

2. Assembly files (3) all have the correct version (2.102.2.20)

When I run #1 above and supply the logon credentials, I still get the error:  ORA-12154: TNS:could not resolve the connect identifier specified

???
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Just to check:  You have a valid tnsnames.ora file in C:\oracle\product\10.2.0\client_2\network\admin

for grins create a system environment variable  TNS_ADMIN and set it to:  C:\oracle\product\10.2.0\client_2\network\admin

then retry.

Author

Commented:
Yes slightwv - I have a valid tnsnames file in my client_2 directory and have a TNS_ADMIN env var set up to point to it. A tnsping confirned that it is pointed to the client_2 dir and was able to resolve the alias.

Could it be a problem that my VS2005 is installed in a Program Files (x86) directory? Everything else seems to work.

Author

Commented:
From Oracle's FAQ:

Additionally: 64-bit Windows users who after doing the above steps still get ORA-12154 should either: 1) Upgrade to the latest version of their Oracle client that contains the fix for bug 3807408, or 2) rename subdirectories in their application that contain parenthesis to remove those parenthesis, in particular: C:\Program Files (x86)\Microsoft SQL Server and C:\Program Files(x86)\Microsoft Visual Studio 8

1) - I'm installed in C:\Oracle\ directory already if that helps.

2) - Reinstall VS2005 or is there another way?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
I don't think you need to reinstall VS.  Maybe tweak for the referenced bug if necessary.  It sounds like there is a version of Oracle where the bug is fixed.

Author

Commented:
Even though my Oracle client works fine in each home (client_1 and client_2) and can connect using a tnsnames.ora file in each NETWORK\ADMIN directory?

Would you recommend:

Installing the new(er) Oracle Client into a 3rd home
Modify PATH
Copy tnsnames.ora into this 3rd home
Modify TNS_ADMIN to point to new 3rd home's tnsnames.ora

??

I'm running SQLPlus & TOAD also and they still seem to be working fine.
Top Expert 2011

Commented:
- i can't be sure if this will work for you as i only done such a setup. thought of sharing what we've done. it might not be the correct way but it works.
- we've this one 32 bit propritery app that we need to install on our windows2008 64bit server. it's install on Program Files (x86).
- since also have other application that is 64bit, we have to install both 32 and 64bit instant client. 64bit instantclient is installed to oracleclient folder, oracle home is sets to that and tnsnames is also copy over to that folder under network/admin.
- after numerous attempt to make the 32bit instant client to work, we finally extract it into the 32bit app folder itself which is Program Files (x86)\app\. and it fly.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Would you recommend:

I'm not a huge fan of the TNS_ADMIN variable.  I always create a tnsnames.ora file in each HOME I have.  It's just a personal perference.  If I need some 'new' client to connect to some new database, I don't have to touch a shared configuration file and possibly break other running apps if I mess it up.

I've not had to mix 63bit and 32bit clients so am not the right person to comment.  Hopefully the post by OP_Zaharin will help since they seems to have lived through this.

So you or your company have an Oracle Support contract?  You might open an SR with them to see if you are hitting the bug and what the 'official' solution is.
Top Expert 2009

Commented:
I recommend only running 2 clients (a 32-bit and 64-bit).

If you want more help, please post your environment variables here (the output of the "set" command in dos).

Author

Commented:
Ok, I posted a SR with Oracle Support. We'll see what they come up with.

Author

Commented:
From Oracle Support:

Windows 7 does not support 10.2.0.2.x software. You must move to 10.2.0.5 or later. In your case this means applying the 10.2.0.5 patch to your 10.2.0.2.20 Oracle Client Software home.

If you review the ODAC installation instructions here...

http://www.oracle.com/technetwork/database/windows/install10202-083849.html

you will see that Windows 7 is not mentioned as being supported. Also, there is additional information here...

Statement of Direction: Oracle Database 10g Release 2 Client (10.2.0.5) with Microsoft Windows 7 and Windows Server 2008 R2 (Doc ID 1061272.1)


=== ODM Action Plan ===

Apply the 10.2.0.5 patch to your 10.2.0.2.20 Oracle home and retest:

Patchset 8202632 - 10.2.0.5.0 PATCH SET FOR ORACLE DATABASE SERVER

NOTE: This is a database patch that is ALSO used to patch Oracle Client Software.

Retest and report the results. If the problem still occurs once you are on supported software, we can do an Oracle Web Conference and I will take a closer look.
Downloaded and installed the 32-bit Windows version of the above patchset and everything works fine now!

Author

Commented:
Solved via SR with Oracle Support.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial