Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Server 2005 32 bit Windows Server 2003 64 bit SSIS Oracle TNS could not resolve connect identifier

Posted on 2009-05-08
3
Medium Priority
?
365 Views
Last Modified: 2013-11-29
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.
0
Comment
Question by:allynmoeller
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 14

Expert Comment

by:wolfman007
ID: 24336751
An Oracle linked server on Windows Server 2003 64-bit and SQL Server 2005 64-bit should be OK.

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
0
 

Author Comment

by:allynmoeller
ID: 24338424
how did you get both the 64 and 32 bit Oracle clients to cooexist?
0
 

Accepted Solution

by:
allynmoeller earned 0 total points
ID: 24546477
moved back to 32 on 32.  couldnt get the integration services packages and the linked server to function.  if one would work then the other would not.
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I was prompted to write this article after the recent World-Wide Ransomware outbreak. For years now, System Administrators around the world have used the excuse of "Waiting a Bit" before applying Security Patch Updates. This type of reasoning to me …
Know the reasons and solutions to move/import EDB to New Exchange Server. Also, find out how to recover an Exchange .edb file and to restore the file back.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question