0

I downloaded ODAC122010Xcopy_x64.zip from https://www.oracle.com/database/technologies/odac-downloads.html and installed it.

install.bat oledb c:\oracle myhome true

And created an entry in c:\oracle\network\admin\tnsnames.ora.

Then I launched "Sql Server Import and Export Wizard", selected the data source of "Microsoft OLE DB Provider for Oracle", clicked "Properties...", filled the information and clicked "Test connection". However, it got the following error?

enter image description here

ca9163d9
  • 27,283
  • 64
  • 210
  • 413

1 Answers1

0

"Microsoft OLE DB Provider for Oracle" is 32-bit but you installed the 64-bit Oracle client. You have to install the 32-bit Oracle Client.

Apart from that, "Microsoft OLE DB Provider for Oracle" is deprecated for ages, you should not use it. Better use "Oracle Provider for OLE DB".

In case you need both, the 32-bit and the 64-bit Oracle client, follow this instruction: BadImageFormatException. This will occur when running in 64 bit mode with the 32 bit Oracle client components installed

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • I downloaded the file from https://www.oracle.com/database/technologies/instant-client/microsoft-windows-32-downloads.html and followed the steps in the link. However, the DTSWizard quit after I select "Microsoft OLE DB Provider for Oracle" and clicked the button "Properties...". – ca9163d9 Feb 28 '20 at 19:29
  • As I have written the Microsoft OLEDB provider is anicent - it does not work anymore with Oracle client 19. Try version 12.1 or 12.2 they should still work. – Wernfried Domscheit Feb 28 '20 at 19:40
  • There is no option of "oracle client"in the drop down list of sql server import export wizards. Besides Microsoft oledb provider, another option is .net client for oracle. I just need to import a table from oracle to sql server. – ca9163d9 Feb 28 '20 at 19:50
  • The instant client does not contain the OLEDB driver, you have to install the ODAC package (and include the OLEDB of course) – Wernfried Domscheit Feb 28 '20 at 19:57
  • I cannot install the ODAC package. It failed at the validation step on my company computer. I'm trying the .Net Framework Data Provider. https://stackoverflow.com/questions/60459068/property-value-is-not-valid-keyword-not-support-host – ca9163d9 Feb 28 '20 at 20:51
  • The Microsoft .Net Framework Data Provider is also deprecated. However, it is still working with Oracle Client 19, so it may work better than the OLE DB. – Wernfried Domscheit Feb 28 '20 at 21:15
  • Is there any other tool that can pump data from Oracle to Sql Server? – ca9163d9 Feb 28 '20 at 21:29
  • Yes, but the problem will remain. No matter which tool you use, it has to open a connection to your Oracle Database. – Wernfried Domscheit Feb 29 '20 at 11:38