Issue importing data from Oracle 8i to SQL Server 2008 32 bit and 64 bit..plz help

Question

For my test system I am running Windows XP with SQL Server 2008 Std 32 bit installed.
I also have the Oracle Instant Client version 10.2 installed.
I can create a system DSN to the Oracle database w/out any issues.
I can connect to the Oracle database via MS Access 2003 using an ODBC connection.
I can also connect to the Oracle database using Toad.
When trying to connect to Oracle via linked server with SQL Server 2K8 there is not an option to connect with ODBC. Any suggestions would be greatly appreciated!

Answer 1

Hello,

If you have installed the Oracle client and created an ODBC DSN already, then you just have to create the linked server with the following instructions:

1. On the “General Page” select “Oracle Provider for OLE DB” as the provider and type the name of the DSN on the “Product Name” and “Data source” fields.

2. On the “Security” page, select the “Be made using this security context” option, and provide login and password for authentication to the Oracle server.

3.  On the “Server Options” page set “RPC” and “RPC Out” to True.
Hope this helps.

Regards,
Alberto Morillo
SQLCoffee.com

Answer 2

Thank you sir!

Regards,

Don E.

Answer 3

hi please see the link given here, it may help you.

http://www.sqlservercentral.com/Forums/Topic330866-150-1.aspx#bm418107