x

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

more ▼

asked Dec 13, 2009 at 11:25 AM in Default

fred nieuwets gravatar image

fred nieuwets
25 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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.

more ▼

answered Dec 14, 2009 at 03:40 PM

Jonathan Kehayias gravatar image

Jonathan Kehayias
283 1 1 2

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 15, 2009 at 07:49 AM

fred nieuwets gravatar image

fred nieuwets
25 1 1 1

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1951
x378
x66

asked: Dec 13, 2009 at 11:25 AM

Seen: 2041 times

Last Updated: Dec 13, 2009 at 12:35 PM