• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1521
  • Last Modified:

Connecting to Oracle 9.2 Database from ASP.NET Using MSDAORA OleDB provider

I am trying to connect to an Oracle 9.2 database from an ASP.NET application using the MSDAORA OleDB provider. It was working for a couple of days, then it mysteriously stopped working.

Just to run through my setup.

I have the Oracle client tools installed in a directory calle Ora92. I have granted the ASPNET user full access to this directory and its subfolders.

I have set the following registry settings:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSDTC\MTxOCI]
OracleOciLib = oci.dll
OracleSqlLib = orasql9.dll
OracleXaLib = oraclient9.dll

In my tnsnames.ora file, I have the following:

ORATEST1.AD.AIB.PRI =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 200.13.10.129)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = oratest1)
    )
  )

In my ASP.NET application, I define the following connection string in my web.config file:

      <add key="StagingConnection" value="Provider=MSDAORA.1;Password=password;User ID=username;Data Source=oratest1" />

In the application, I create the connection as follows:

            Dim dbConnection As New OleDbConnection(ConfigurationSettings.AppSettings("StagingConnection"))
            dbConnection.Open()

On the Open line, I get the following error:

ORA-12154: TNS:could not resolve service name
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.OleDb.OleDbException: ORA-12154: TNS:could not resolve service name


When I try to open the connection in the Visual Studio Server Explorer, I can browse tables and stored procedures. I have copied this connection string from the Server Explorer to web.config. This leads me to suspect that it's related to the ASPNET user's permissions, but I've given it full access to the Ora92 directory.

This is especially annoying as it was working for a few days without any problems.

Thanks in advance for any help.

Regards,

James

0
lostcarpark
Asked:
lostcarpark
  • 4
  • 4
1 Solution
 
schwertnerCommented:
A very experirnced guy shared his experience at

http://www.experts-exchange.com/Databases/Oracle/Q_21827319.html
0
 
lostcarparkAuthor Commented:
After some more digging, I have found something weird.

There are two versions of the Oracle Client installed on my PC. The older one, Oracle 7 is required for another application, and is installed in a directory C:\ORANT. This seems to be set as the default.

There is a registry key for this version; [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID0] which has a Name of "DEFAULT_HOME and a path of "C:\ORANT"

The second instance, Oracle 9.2 is installed in C:\Ora92. This has a registry key [HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\ID1] with a Name of "Ora92" and a Path of "C:\Ora92".

In various parts under the ORACLE subkey, DEFAULT_HOME and parameters from it are specified as the default.

I edited the tnsnames.ora file in the Oracle 7 directory and added in the setting for my server from Oracle 9.2, and my application suddenly started working.

However I'm now concerned about which client is being used, and whether it will cause any other problems. Our servers will only have one version of the client, so the issue shouldn't arise there, but I want to make sure I'm not doing anything that will cause problems when we migrate.

Thanks for your help,

James
0
 
actonwangCommented:
try to issue this in the command line to see if it works:


tnsping ORATEST1
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
lostcarparkAuthor Commented:
Thanks for that. I've copied the out below. It seems to suggest it's using the Ora92 version, at least under my login. Just to check, I tried commenting out the entry in the tnsnames file in the OraNT directory (the version 7 one), and tnsping still worked. I guess I should try writing a test ASP.NET script to try shelling to run the command under that user and see what happens.

Any other suggestions?

Thanks,

James




TNS Ping Utility for 32-bit Windows: Version 9.2.0.1.0 - Production on 09-MAY-20
06 09:15:59

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
C:\Ora92\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)
(HOST = 200.13.10.129)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = oratest1))
)
OK (90 msec)
0
 
actonwangCommented:
yes. You can create 2 different names in both tnsnames.ora for your test to see which one is used.

much as same reason, you can create SAME entry in both tnsames.ora to make sure you alwasy get service name resovled:)


Acton
0
 
lostcarparkAuthor Commented:
Thanks for the help. I think the setup on my PC is a little messed up, with some confusion between Oracke 9i and Oracle 7 components, but it seems to be working.

More importantly, I have set up our test server, which thankfully only has the Oracle 9i client, so none of the messy setup issues, and it worked first time.
0
 
actonwangCommented:
what is your good approach to handle it right now? curious to hear...
0
 
lostcarparkAuthor Commented:
I have the settings for "oratest1" in the Oracle 7 tnsnames.ora file as well as in the Oracle 9i one.

The registry settings seem to set Oracle 7 as the default client, so it seems to be picking up that tnsnames file. I don't want to change this, as I suspect it would break the other application.

However, the registry settings for the OLEDB provider clearly specify the Oracle 9i DLLs, so I'm fairly sure it's talking to the right client, just getting the network information from the wrong place.
0
 
actonwangCommented:
I c. it was somewhat "messed up"...
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now