Can't connect to Oracle in dtsx job

views:

327

answers:

Can't connect to Oracle in dtsx job

When MS-SQL Server 2000 dtsx job tries to run, gets error "Ora-12154 TNS could not resolve service name" Tnsping to the oracle service to connect replies OK The Windows 2003 x64 Server that runs the dtsx jobs has 32 bit Oracle Server, ODAC 32 bit and Oracle Client 32 bit installed. Thank you for your concern ,

Kayhan YÜKSEL

The TNSNames setting on a machine may be user specific. i.e. The connection might work logged in as you, but fail when it runs under the SQLAgent user. At our location, we use a networked TNS Names file, and set a system environment variable on the server. This forces it to use the same TNS names file for all users:

TNS ADMIN = \\server\path\ORACLE

Bill 2009-06-29 14:19:34

do we have to add TNS ADMIN like ORACLE_HOME (TNS_ADMIN, using underscore) to the env. variables? BTW, no ORACLE_HOME is defined at env. variables of the source server.

2009-06-29 14:39:18

You don't have to have environment variables if the TNS names file is local to the server - only if the server needs to find it somewhere else on the network.If local, at least make sure the SQLAgent account has read access to the file.

Bill 2009-06-29 16:04:47

Not quite right. You need TNS_ADMIN if your tnsnames.ora file is not in the default location for the current ORACLE_HOME. It has nothing to do with whether the file is somewhere else on the network. In fact, I've run into problems having a shared tns_admin directory. It forces all who use it to share a sqlnet.ora as well, which may be problematic at times. What you don't want to do in Windows is set the ORACLE_HOME environment variable - Oracle gets that from the registry for the current ORACLE_SID.

DCookie 2009-06-29 16:25:38

Thanks for the clarify DCookie.

Bill 2009-06-30 02:13:21

Kayhan YÜKSEL,

You have mention SQL 2000 and dtsx. Which one is it?
You have mentioned 64 bit server. Is SQL also 64 bit?
You have also mentioned "job". Is this SQL Agent job?

If this is SQL 2005/8 64 bit and this is indeed a scheduled job, then you are having the famous 64 bit Vs. 32 bit issue.

The SQL agent will run in 64 bit and cannot see any of your 32 bit drivers. Do you have the 64 bit Oracle data provider installed?

If the tnsnames.ora is not in the default location, you will need the TNS_NAMES environmental variable set. Also make sure that you are referring to the Oracle server with the correct registered name in your SSIS package.

Raj

Raj 2009-06-29 17:56:15

ansaurus

tags:

views:

answers:

Can't connect to Oracle in dtsx job

related questions