| Author |
Topic |
|
dbthj
Posting Yak Master
143 Posts |
 Posted - 2011-06-07 : 19:49:06
|
| The import wizard is usually simple to use...even from oracle. Not so if you are on x64. I gather from various threads on the internet this has been true since SQL 2005 x64. I'm on Windows (vmware) 2008 R2 with a 64bit SQL instance and want to use the import wizard to get data from oracle. I gather the problem comes from the fact that Microsoft does not provide a 64 bit ODBC driver for oracle. The threads that come closest to providing a solution deal not with Importing, but with the creation and use of linked servers. Some say to install BOTH the 64 and 32 bit oracle clients. Some don't. Some talk about editing a bunch of stuff in the registry and copying a bunch of folders from one oracle client into the other. None of it sounds like something I would like to try. I can use SQLPLUS just fine on the windows box. I can create and test system dsn's just fine. My collegue called Microsoft support for help and they blew him off saying it's an Oracle problem. Well, I don't believe it. All of the oracle pieces work just fine. Trying the import wizard gives me the message: =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-Microsoft Data Link Error---------------------------Test connection failed because of an error in initializing provider. Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation.Provider is unable to function until these components are installed.=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=Has anybody run into this and found a way to use the Import Wizard from oracle to SQL 2008 x64? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-07 : 22:57:49
|
| It's an Oracle problem because Microsoft does not need to provide drivers for other dbms'. Oracle can create their own driver to be used on a Windows platform. Why is it up to Microsoft to have to create an Oracle driver? The driver wouldn't just be used for SSIS, it could be used for lots of things that deal with Oracle data access on Windows x64. Doesn't Oracle have an import/export type product that you can use to get the data to csv format and then use SSIS/bcp/import wizard to get it into SQL Server?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-06-08 : 01:17:29
|
| I'll have to disagree with you on that one. Oracle has never had any particular interest in making it easy for people to put their data into another DBMS. Microsoft, on the other hand, did like to see oracle data move into SQL. They made it very easy to do that and I appreciated it. Now, it appears Microsoft is playing Oracle's game and being insular. Beyond that, it is always disappointing to see functionality decrease, rather than increase. Oracle has always out-nastied Microsoft in almost every aspect. Perhaps that is changing. |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-06-08 : 11:08:34
|
| Two more things.1. The SQL Import tool says it can import from oracle. It can't. It should do what it says. And the errorit gives when it fails is to install the oracle client. The client is there, the networking components are there and they work fine. The import tool still does not do what it claims it will do. 2. As this isn't a Microsoft site, responsibility is fairly irrelevant. I'm asking DBAs if y'all have seen thisand dealt with it. If you haven't, then that's ok. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2011-06-08 : 13:25:59
|
| The import tool is only as good as the drivers. The Oracle driver is for 32-bit systems, and they do not have a 64-bit driver. So you can either use a 32-bit system or find another driver that will work if you want to use that tool. If no other driver exists, then you'll need to use another tool.Just because Oracle doesn't have any interest in making things easy doesn't mean it's Microsoft's responsibility to create a 64-bit driver for Oracle.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-06-09 : 12:03:40
|
| Nope, not Microsoft's responsibility - unless they build a tool that says it will handle the task.Oracle always wants to make things a pain in the tail -- even for their own clients. No surprises there. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
dbthj
Posting Yak Master
143 Posts |
Posted - 2011-06-09 : 12:51:49
|
| Nope. It just says it will work for oracle. And it doesn't say (only 32 bit on tuesdays after 6 pm on South end of New York) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Import from oracle to 64 bit SQL