question

fred nieuwets avatar image
fred nieuwets asked

SQL Server EM-2005 64bit and ODBC oracle 9 / 10g

Hi,

We are migrating the 2000 server to 2005 Enterprise Manager 64bit (running under windows 2003 64bit). The first instance was no problem and it is working fine. All the dts from SQL 2000 are working perfect. We are still using the DTS because it works in production. In the near future we will rebuild them in SSIS. The second instance is the problem. Databases, DTS to legacy, jobs, security and so on no problem. The problem is that in the dts we use ODBC connection to the source database Oracle 9... No we are facing huge problems: we can't get the ODBC working from DTS, SSIS to the oracle database. Does anyone has the solution for working from 2005 to oracle databases with dts. I have read a lot of solutions as install 10g clients, install not in (x86) path, ORACLE_HOME path, and so on.

I hope someone can give me the right solution for this problem.

Kind regards

Fred

sql-server-2005oracledts
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

2 Answers

·
Jonathan Kehayias avatar image
Jonathan Kehayias answered

You generally face two problems using Oracle with SQL Server x64. The first is that the tools are 32bit, so to manually run a package for development or testing purposes, you have to have the 32 bit Oracle client installed as well as the 64bit Oracle client installed. When the packages actually run using DTEXEC, they do it with the 64bit driver if the 64bit version of dtexec is used. The second problem which you have already found is that the Oracle client takes a dump if you call it from a path that has a parenthesis in it (ie (x86)). The solution is to install the client outside of that path, but I also had to copy the Microsoft SQL Server (x86) path to Microsoft SQL Server x86 and then call the DTEXEC executable from there to get it to work.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

fred nieuwets avatar image
fred nieuwets answered

I have it all working now without (86) parenthesis problem which I never faced. What have I done:

Download software InstantClient 10.2.0.4 voor 32bit InstantClient 10.2.0.4 voor 64bit

Unzipped in d:\instantclient_10_2_32 and d:\instantclient_10_2_64

Changed the enviroment variable (windows) adding to PATH variabele d:\instantclient_10_2_32;d:\instantclient_10_2_64

Added an new variable TNS_ADMIN with the value where the tnsnames.ora is located (in our case c:\beheer)

Install drivers from unzipped folders (command line to see what happens). First the 32bit and then the 64bit (ODBC_INSTALL.EXE)

Check regedit for the ODBCINST.INI (SOFTWARE\ODBC and SOFTWARE\SYSWOW64\ODBC) if the path d:..... is installed

Most important check if the MFC71.dll and MSVCR71.dll are installed in the SYSWOW64 directory. If not download or copy them from another machine. We where missing the MFC71.dll

Reboot the server (standard if we change anything and to be sure that nothing is left behind in the registry, memory and do on)

Create an ODBC driver using the controle panel\administrative tools\ODBC manager (this one is the 64bit ODBC administrator) and create an ODBC driver using the run : c:\windows\syswow64\odbcad32.exe (this is the 32bit administrator)

I have tested it with DTS, SSIS and it runs perfect. Also the DTS are running with DTSRun from the schedular.

Thanks for your replies.

Fred Nieuwets

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

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

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.