OLE DB provider for ODBC not listed in Import/Export Wizard

Question

I'm trying to use import/export to import data from oracle to sql2008, however I couldn't find the microsoft OLE DB provider for ODBC in the List.

I have installed the Oracle odbc driver and could see it and sucessfully tested connection using obdc by creating a data source.

Anyone know why the data source it's not appear in the sql2008 native driver which this can be done in sql2000.

Thanks

Note: I have do a test using test file test.udl it could shown the odbc, however not in import/export wizard.

Answer 1

Hello,

The Data Import/Export Wizard don't support ODBC currently, only OleDb and .NET data provider; you have to use "Oracle Provider for OLE DB" instead.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Hi Olaf,

Thanks, I just upgrade from sql2000 to sql2008, therefore just wonder it still working.

For "Oracle Provider for OLE DB" , do I need to setup or configure anything? I have installed the Oracle 11g client but not sure how to use this.

Thanks for your advice.

Answer 3

If you already have installed the Oracle client and this one is working, then you should find the Oracle OleDB provider in the provider list of the wizard.

As configuration you only need the TnsOraNames file for Oracle, but if the client works, everything should be fine.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

Thanks Olaf,

I have try to use the Oracle OleDB provider when using import/export, under the properties there is 1. data source,2. username and password.

First, do i need to enter the data source, if refer to what in the tnsnames? eg.

PK1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.1.15)(PORT = 1527))
    )
    (CONNECT_DATA =
      (SID = pk1)
      (SERVER = DEDICATED)  )  )

Second, If I do not enter the data source, it give me error failed because of an error in initializing provider.

Sorry that I have not try this before so not quite famailer how to use it.

Regards

Answer 5

PK1 is the TNS datasource name, so use this one.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 6

Hi,

I had tried enter the data source as PK1 and do a test, it give me Test connection failed because of an error in initializing provider.

I wonder any tace to find out what going wrong, thanks.

 :(

Answer 7

Do you may have a 64 bit OS? If so, you need also to install the 32 bit Oracle provider, because the Wizard is a 32 bit application, which can only use 32 bit provider.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 8

Hi Olaf,

Thanks, you are right, my OS is 64bit, I have installed the 64bit Oracle 11g Client it doesn't work, after your advice I installed the 32bit Oracle 11g Client as well.

It seems like working now, many thanks for your help.

Regards

jim.