I'm struggling getting an app that has a connect string that connects to Oracle database to run in QMC. The app runs fine on the server when I run it in QlikView Desktop but when I kick it off in QMC, I get this error:
Connector connect error: SQL##f - SqlState: 08004, ErrorCode: 12154, ErrorMsg: [Oracle][ODBC][Ora]ORA-12154: TNS:could not resolve the connect identifier specified
Here's a full story of what I've done so far to help zero in on the issue. I had to start from scratch when it comes to installing Oracle client on the server. Not an unfamiliar task for me. On the server, I went ahead and downloaded Oracle Database 11g client, set up environmental variables, and added ODBC connection in ODBC Data Source Administrator. I was logged on to server using server’s credentials, reload the app using QlikView Desktop, the app connects and pulls the data without any issues. I was a happy camper at this point, thinking I got this, Oracle client is installed, I can run the app using server's credentials, all is good.
Then I set up a task in QMC thinking all will continue to go fine and dandy, I kick off the refresh of the app in QMC and it fails with the error above. I installed Toad for Oracle software to check that I was able to connect to the database with some software other than QlikView and I connected without any issues. Ran the same query as in QlikView script and Toad produced the results exactly as I expected. I opened the app in QlikView Desktop (all this is done on the server with server's credentials) reload the app, it runs fine, pulls the data in without any issues. I'm thinking, "ok, maybe QMC had a hiccup and failed randomly. Slim chance, but possible." I kick off the app in QMC and it fails. Same error message.
Now at this point, I'm thinking there something that needs to be set up in QMC that I'm not familiar with. Have any of you completed a setup on a server that got QMC to play well with Oracle ODBC connection? Is there something that I need to set up in QMC to get it to run an app with a connect string to Oracle DB? To add clarity, I did absolutely nothing in QMC so if there are some steps that I need to take in QMC to get it to reload an app with Oracle DB connect string, would you please let me know?
Yes, I have fought Oracle Client to get it to work on a QlikView Server. Can't remember everything though, because I stay far away from everything Oracle in the hopes that it keeps working. Been very lucky so far 🙂
Yes. Have a look at your TNSNAMES.INI configuration file. That is the file Oracle Agent is complaining about, and that should contain a defintion for the name you are using in your ODBC connection, whatever the setup method. AFAIK, Toad can use its own connection definition that circumvents the whole Tnsnames confguration. Not sure about that though.
Yes, I have fought Oracle Client to get it to work on a QlikView Server. Can't remember everything though, because I stay far away from everything Oracle in the hopes that it keeps working. Been very lucky so far 🙂
Yes. Have a look at your TNSNAMES.INI configuration file. That is the file Oracle Agent is complaining about, and that should contain a defintion for the name you are using in your ODBC connection, whatever the setup method. AFAIK, Toad can use its own connection definition that circumvents the whole Tnsnames confguration. Not sure about that though.
Thanks for the help! Point 2 had the solution. In the connect string in the script I had the name of the data source contain .WORLD (as in BDNM.WORLD), but in ODBC Data Source Administrator, the TNS Service Name was showing up as DBNM. I opened up tnsnames file and saw that the name is actually DBNM.WORLD - same as in the connect script. I went back to ODBC Data Source Administrator, manually typed in .WORLD to the TNS Service Name clicked Test Connection and it connected successfully. Reran the app in desktop and it reloaded successfully. Reran it in QMC and it reloaded successfully there too.
I guess QlikView Desktop is wise enough to understand that DBNM.WORLD is same as DBNM, but QMC is more rigid. It actually needs the name in the script to match the name in the ODBC Data Source Administrator.
It also works if I deleted .WORLD from the connect string. In other words, if I leave TNS Service Name in ODBC Data Source Administrator as DBNM and have the connect string reference DBNM (instead of DBNM.WORLD), QMC reloads the app without errors.
The lesson is that TNS Service Names have to match exactly between ODBC Data Source Administrator and connect string in QlikView as well as in tnsnames file.
