Issues with Oracle Provider for OLE DB in SQL Server

979595

Hello,

I am experiencing several issues with the oracle driver that allows SQL Server to import data from Oracle. My main problem is that I am trying to install the option for one of my clients that is using Windows Server Standard with SQL SERVER 2008. When you go into SSIS wizard, there is not an option for Oracle Provider for OLE DB (I tried the Microsoft driver but unfortunately that one does not work right either).

I tried installing 10g on this machine but it failed immediately on install when trying to open saying "unknown, unknown, unknown" for reason. Next, I tried installing 11g and this ran correctly but it did not add the option in SQL Server so it didn't make a difference to me (in the install it said that the package contained it).

I have also been using my computer for testing so I wouldn't have to test on their server. I was able to install 10g on my computer successfully and added this added the option in SQL Server that I am wanting. However, I tried to completely uninstall 10g from my machine (first through the uninstall tool from setup, then deleting it from where the folder was stored on my drive, then from the registry in all of the places. Now, if I go into SQL Server the option is still there but if I try to select it I get an error saying the files needed to run aren't there (expected that). What I am wanting to do is get rid of that option completely and install 11g on my personal computer to see if it adds the option in SQL Server.

So my two problems are:
1: How to get the option added on the server's SQL Server for "Provider for OLE DB"?
2: How to get rid of where 10g added "Provider for OLE DB" on my personal computer now that 10g is uninstalled?


Sorry for how complicated this is and maybe I am just making this way more complicated than it has to be!

orafad

976592 wrote:
My main problem is that I am trying to install the option for one of my clients that is using Windows Server Standard with SQL SERVER 2008.

Is the MSSql Server instance 32 or 64-bit? (I think it should say from properties on the db server/top node, in Product name.)

In SSMS, Server objects, Linked servers, Providers. Is OraOLEDB.Oracle listed?

2: How to get rid of where 10g added "Provider for OLE DB" on my personal computer now that 10g is uninstalled?

Possibly it will be "overridden" if you install another instance of the provider, e.g. from a 11g Database product. Only one OraOLEDB may exist on the machine.

But to clean out the installation completely you probably need to do a system restore or re-install.

Check out ODAC packages with Xcopy deployment option, based on Instant client. That's about as lightweight a install of OLE DB you'll get.

979595

It is 32 bit, but I loaded the 64-bit SSIS wizard and it was there, thank you for pointing that out!!

Now I am getting a new error... when I test the connection I get "Test connection failed because of an error in initializing provider. ORA-12154: TNS:could not resolve the connect identifier specified.

I checked the tnsnames.ora file to get the service name KFXXXXXXXX and got the username/password from the client but it is still not connecting to the database.

orafad

As the message says, the name for the connect descriptor could not be resolved (or found). Possibly wrong name given, wrong tnsnames.ora or bad tnsnames.ora file.

Try leaving out the use of tnsnames, by using the format 'hostname:port/service_name' instead where you enter connection name. E.g localhost:1521/xe. Leaving out the port number if std port 1521 is used is possible, e.g. localhost/xe instead.