question

mpereira avatar image
mpereira asked

SQL Server 2005 - linked server Oracle 7.3.4

I have a 64-bit SQL Server 2005 database and a 32-bit Oracle 7.3.4 database on 2 different servers. I would like to create a linked server so that I can access the Oracle data from the SQL box. I have installed the 32-bit Oracle client on the 64-bit SQL box, and can connect to the Oracle server using Oracle's software. But SQL Server does not see the Oracle driver to create the linked server. The Oracle box is being phased out, but I need to copy some Oracle data to the SQL box. Any assistance would be appreciated.
serverlinked
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Fatherjack avatar image
Fatherjack answered
Have you tried the Data Import/Export Wizard - Right click a database in the object explorer, select Tasks and then Import Data. This will let you set the connections to the Oracle source and the SQL destination and then give you the chance to select the data you want to transfer. Finally the wizard will let you execute or save the package to run at a time of your choosing.
8 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Oh boy, I typed a long comment and it is gone because the answer was being converted to comment at the same time. I will redo.
1 Like 1 ·
@mpereira If you don't have SQL Server 32-bit box and cannot use Fatherjack's suggestion to also install Oracle client there and then try SSIS, you can also opt for something like this: Oracle 7.3 is pretty old and its own set of tools is pretty retarded, so it is very hard to imagine that you don't have **Toad**. However, in case if you don't, Quest allows [free trial download][1]. **Toad** has the feature to script out Oracle tables ***and their respective data*** right from the IDE (generates a bunch of plain vanilla insert statements which you can then use to insert into SQL Server with very minor if any modifications). [1]: http://www.quest.com/toad-for-oracle/software-downloads.aspx
1 Like 1 ·
Yes, I tried to import data into SSMS, but the wizard asks for a Data Source. The only plausible one I could pick was ".Net FrameWork Data Provider for Oracle". But that returned the error "System.Data.OracleClient requires Oracle client software 8.1.7 or greater". Thanks for your response.
0 Likes 0 ·
@mpereira Oracle 7.3.4 is pretty old, and is no longer supported, but if you have a way to install a syutable version of SQL Server 32-bit on dev box with XP then you can also install Oracle Client on the same dev box and try using SSIS or openrowset. Another thing to try would be to use **Toad**. If you have Oracle then it is very hard to imagine that you can work without **Toad** as the Oracle's own tools for version 7.3 are pretty retarded. Toad does allow scripting Oracle tables with data right from the IDE. If you don't have Toad handy, Quest allows free [trial version download][1]. [1]: http://www.quest.com/toad-for-oracle/software-downloads.aspx
0 Likes 0 ·
is it not possible to install the client software? If not then you may have to try to import the data to SQL on a 32bit box and see if the results are any better
0 Likes 0 ·
@Oleg, sorry about that. A bit too zealous on the tidying up!! Great suggestion to use TOAD.
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.