Question: Why can my local code execute a ORAOleDB.Oracle connection but I get no provider found when I run Anonymous execution though the same computers IIS Website.
So I am publishing a website on my local machine to connect to different databases. The code works fine connecting to a database and returning an error code that I parse. I just want to make sure the TNS Listener is up and Oracle Listener is up not actually log into it.
Locally : Code Passes
Push to IIS : Code Fails
However, when I publish the code to my IIS and run the same exact code I get the OraOledb.Oracle provider not found on local machine.
When I publish my Website I receive the following message for every Oracle Database I try:
The 'OraOLEDB.Oracle' provider is not registered on the local machine.
I have registered the dll:
cd C:\ProgramData\oracle\oracle11g\BIN
regsvr32 OraOLEDB11.dll
The following permissions on the folder where oracle is installed
C:\ProgramData\oracle has the following users:
IIS AppPool\DefaultAppPool
IIS_IUSRS
IIS AppPool\ASP.NET v4.0
All these users have Full Control
My Code is as follows. Very simple it just takes the database name and makes a dummy string.
private void ExecuteDatabase(string database)
{
string openstring = "Provider=OraOLEDB.Oracle;Data Source=" + database + ";User Id=x_dummy;Password=x_dummy;Connection Timeout=5;";
string msg = String.Empty;
string Elapsed = String.Empty;
string Result = String.Empty;
using (OleDbConnection connection = new OleDbConnection(openstring))
{
//Timestamp for elapsed time counting
DateTime stTime = DateTime.Now;
try
{
// Try to connect. If succeed, then close, and create
// an OK message. x_dummy user and password is improbabile.
// If connection not successfull, there issued an Oracle
// exception. We need to parse the exception.
//connection.ConnectionTimeout = 5;
System.Diagnostics.Debug.WriteLine("Connection Open");
connection.Open();
System.Diagnostics.Debug.WriteLine("Connection Opened");
// If succeeded...
msg = "OK";
//.. then closes
connection.Close();
}
// If connection is not succesfull, we get the errormessage.
catch (Exception ex)
{
//We need the first part of message, this is an Oracle message like this: ORA-01017
//msg = ex.Message.Split(':')[0];
msg = ex.Message;
}
//Timestamp for elapsed time counting
DateTime etTime = DateTime.Now;
//Calculate the elapsed time
TimeSpan ts = etTime - stTime;
//Get the millisecs from elapsed time
Elapsed = ts.Milliseconds.ToString();
//Get the Result String
Result = "{" + databaseResult(msg.Split(':')[0]) + "}";
Result = Result + msg;
}
}
private String databaseResult(string msg)
{
string ret = String.Empty;
switch (msg)
{
//kind os listener answers
case "ORA-12154":
ret = "No Oracle listener found";
break;
case "ORA-12514":
ret = "No TNS listener found";
break;
case "ORA-12541":
ret = "No listener ";
break;
//ORA-01017 is only good response for us. It means
//that listener is good, but username and passwor is not.
//This procedure gives back OK message and elapsed time in this case
case "ORA-01017":
ret = String.Format("OK");
break;
case "OK":
ret = String.Format("OK");
break;
//In default case this procedure doesn't found listener.
default:
ret = "No Oracle listener found";
break;
}
//returns the result of connection
return ret;
}