11 aprilie 2012 02:50I'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.
Note: I have do a test using test file test.udl it could shown the odbc, however not in import/export wizard.
11 aprilie 2012 04:27
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.
12 aprilie 2012 01:13
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.
12 aprilie 2012 04:42
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.
12 aprilie 2012 06:35
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.
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.1.15)(PORT = 1527))
(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.
12 aprilie 2012 07:09
13 aprilie 2012 01:23
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.
13 aprilie 2012 04:21Do 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.
- Marcat ca răspuns de KJian_ 17 aprilie 2012 08:15
18 aprilie 2012 03:40
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.