Desktop succeeds/ Publisher task fails using Oracle ODBC with TNS-12564

[andy]

Hi folks,

While running Qlikview desktop (version 9 SR6) my script can make use of the machines Oracle ODBC-driver without any problem. BUT when Qlikview Publisher (version 9 SR6) executes a task running the very same script it fails!!!!

The Qlikview document-log which I have enabled just hangs before the very first SQL-statement. There is a SQLNET.txt log created when the publisher fails, it says:

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ADE)(CID=(PROGRAM=C:\PROGRA?1\QlikView\Qv.exe)(HOST=SRV01669)(USER=m37401)))(ADDRESS=(PROTOCOL=TCP)(HOST=10.XXX.XXX.XX)(PORT=1521)))

VERSION INFORMATION:

TNS for 64-bit Windows: Version 10.2.0.4.0 - Production

Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 10.2.0.4.0 - Production

Time: 15-NOV-2010 14:26:48

Tracing not turned on.

Tns error struct:

ns main err code: 12564

TNS-12564: Message 12564 not found; No message file for product=NETWORK, facility=TNS

ns secondary err code: 0

nt main err code: 0

nt secondary err code: 0

nt OS err code: 0

OK so here is the background information:

The Publisher machine is a Windows 7 64-bit system and the DB-machine is a 32-bit windows running Oracle 10.2. I installed the 10.2.0.4 64-bit client software from Oracle ( I had to tweak the oraparam.ini to allow installation on that OS, according to hints in Oracle forums)

First I installed on my developer machine which is another Win 7 64-bit machine and connected to my developer DB which is an Oracle XE (32-bit). By then I could connect with a connection-string looking like:

DBHost:1521/ADE

This works on the real Publisher machine as well when running DataDirect 6.1 x64 ODBC-driver. (We don't want to use that driver since it costs 5000€)

When changing to the Oracle driver instead of Datadirect's I couldn't use that connection-string anymore (got a ORA-12514 error) but had to create a file called OraHome/Network/Admin/tnsnames.ora with a content looking like:

**************************************************

# TNSNAMES.ORA Network Configuration File: C:\oracle\ora92\network\admin\tnsnames.ora

# Generated by Oracle configuration tools.

ade.srv01393 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = 10.XXX.XXX.XX)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = ade.srv01393)

)

)

**************************************************

By then I could make use of a connection-string looking like ade.srv01393. It works like a charm when testing the connection in the ODBC administrator and when reloading data in Qlikview Desktop but it fails when reloading data in a Publisher task on the very same machine.

Are there any more files I have to modify other than the tnsnames.ora?

I also downloaded the 1.1 GB Oracle 10.2.0.5 patch for windows 7 but that could not be installed on just the client software but required the whole DB to be present so never bothered with that since I just want to make use of the ODBC-connection.

I guess this failure is due to some small configuration that I as a Oracle-newbie has missed.

Any hints are appreciated very very much!

Brgds

Andy

[#2]

You might try the MS-Oracle ODBC drivers, they were the only ones that I could get working. I believe the driver was installed with the Oracle 10g client.

[#3]

1. Install oracle client on Publisher mechinge.

2. Go to $ORACLE_HOME/network/admin/ on Oracle server

3. copy tnsnames.ora file from oracle server (find it in $ORACLE_HOME/network/admin/tnsnames.ora) to client $ORACLE_HOME/network/admin directory

4. you can test network connectivity from client to server using C:/tnsping ade.srv01393 from cmd prompt

5. If you still see the TNS error code 12564, do couple of things like change

post the tnsnames.ora file from server

Good Luck. Let me know if you still having any issues.

[andy]

Hi,

I don't have access to the server files. But I cannot see why it shall be a problem in tnsnames.ora when it works in ODBC administrator when I test the connection with the very same ade.srv01393 string and it also works in Qlikview desktop when I run the file stand-alone.

Another thing that I noticed was that the first time I run the script stand-alone I got prompted for the password. If that prompt shows up in publisher that might be the issue...

/Andreas

ps. Haven't tried the MS Oracle driver yet

[blaise]

It looks like you have credential problems - http://tns-12654.ora-code.com/?

If you have the possibility to log on to the server using Publishers account (if it's not the same as yours) try running the script from Qv Developer and see if you're asked for credentials.

[andy]

Ah! That is a very good point!

It is different accounts so this sounds like the way to the solution

I'll let you know when I've tried.

/Andy

[andy]

Hi again,

I logged in as the user running the publisher services. I could fetch data from the Oracle DB using Qlikview desktop without any problem. What's wrong with the publisher?

/Andreas

ps.Cannot find the MS Oracle driver on that machine.

[blaise]

Sadly, I don't know 😞

I would try to create a very simple sql query, something like select somefield from sometable where rownum=1 so you are 100% sure its the connection that causes the issues.

[#9]

Hi,

Looking at your original SQLNET.txt from Publisher.

• It is interesting to see the name of the QV.exe = developer tool. So you are not looking at Publisher's error log
• Service name is "ADE", apparently it lacks the full name "ade.srv01393" . Try to make a new tnsname entry with name without dot

-Alex

***********************************************************************

Fatal NI connect error 12514, connecting to:

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=ADE)(CID=(PROGRAM=C:\PROGRA?1\QlikView\Qv.exe)(HOST=SRV01669)(USER=m37401)))(ADDRESS=(PROTOCOL=TCP)(HOST=10.XXX.XXX.XX)(PORT=1521)))

[andy · Accepted solution]

Hi,

After playing around on the system the error in the publisher-log changed to a system error 1114. That helped me find this thread:

http://forums.oracle.com/forums/thread.jspa?threadID=393762

A reboot of the server made me happy too, now it works!

The explanation I can think of is that the publisher is run by a service and the Oracle ODBC depends upon environment variables so that might be why the service needs a restart to get the new ODBC driver work properly but the user I log in with (running the desktop) does not. Logging in with the account running the publisher service allowed me to run the desktop successfully which was very strange though.

Alexandru, you are right there is something weird with the logs. I just have to go back to the server and play with the tnsname-entrys. The strange thing is that on my test-system and on the old publisher (QV7) the connection strings always looked like srv01669:1521/ADE and there was no tnsnames.ora file at all. Now I have to go through all the apps when moving them to the QV9-system.

Thanks for all the responses, this forum is great!

/Andy