allynmoeller
asked on
SQL Server 2005 32 bit Windows Server 2003 64 bit SSIS Oracle TNS could not resolve connect identifier
I am migrating a 32 bit instance of Windows/SQL Server to 64. In my previous environment all connectivity to my Oracle database worked fine. Since I have migrated I cannot get connectivity established and even when I think that I have it (no errors), my queries return no data.
So far I have tried installing 64 bit Windows/SQL Server 2005/Oracle client 10g...in this configuration linked servers seem to work (but return no data) and my SSIS packages will not connect. Through research I have discovered that certain components within SQL 2005 are still 32 bit and thus not able to see the Oracle 64 bit components. Some suggested installing the 32 bit Oracle client (so 32 can see 32 and 64 can see 64), but this hosed my entire Oracle client install.
I have now uninstalled 64 SQL Server/Oracle and installed 32 SQL Server and 32 Oracle client and though in every case the Oracle client can connect to the Oracle database I receive TNS:could not resolve conenct identifier when attempting connections through SSIS (tnsping and ODBC connect without error)
What are my options? Would an upgrade to SQL 2008 resolve these issues or would I be better off downgrading to Windows Server 2003 32 bit.
Thanks.
So far I have tried installing 64 bit Windows/SQL Server 2005/Oracle client 10g...in this configuration linked servers seem to work (but return no data) and my SSIS packages will not connect. Through research I have discovered that certain components within SQL 2005 are still 32 bit and thus not able to see the Oracle 64 bit components. Some suggested installing the 32 bit Oracle client (so 32 can see 32 and 64 can see 64), but this hosed my entire Oracle client install.
I have now uninstalled 64 SQL Server/Oracle and installed 32 SQL Server and 32 Oracle client and though in every case the Oracle client can connect to the Oracle database I receive TNS:could not resolve conenct identifier when attempting connections through SSIS (tnsping and ODBC connect without error)
What are my options? Would an upgrade to SQL 2008 resolve these issues or would I be better off downgrading to Windows Server 2003 32 bit.
Thanks.
ASKER
how did you get both the 64 and 32 bit Oracle clients to cooexist?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Have a look at the following websites
How to setup linked servers for SQL Server and Oracle 64 bit client
http://www.mssqltips.com/tip.asp?tip=1433
Also the fix I have for sql 2005 64 bit environment connecting to Oracle 9i is to install Oracle 11g 32 bit client as well as 64 bit client.
Oracle 32 bit client is used for SSIS connection manager
Oracle 64 bit client is used for Oracle Linked Server
from
How to setup linked servers for SQL Server and Oracle 64 bit client
http://blogs.mssqltips.com/forums/t/161.aspx