topic Re: listener was not given the SERVICE_NAME in CONNECT_DATA in QlikView

listener was not given the SERVICE_NAME in CONNECT_DATA

Fri, 12 Feb 2016 18:59:17 GMT

Hi,

I am trying to reload a qvw file on QMC and it fails. On generating the log file this is the error I get

Error: SQL##f - SqlState: S1000, ErrorCode: 12504, ErrorMsg: [Oracle][ODBC][Ora]ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

I installed Qlik desktop on the server and reload manually from qlik desktop and it ran fine. I have the service name in tnsnames.ora

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Anonymous — Fri, 12 Feb 2016 19:38:15 GMT

How many servers do you have ?

Sounds like you tnsnames.ora file on the server the QMC does its reloads on is different to the one on the server that your manual reload ran on.

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Fri, 12 Feb 2016 19:38:49 GMT

Hi Bill,

I have only one server

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Fri, 12 Feb 2016 22:33:35 GMT

When I copied the tnsnames.ora file to the folder which contains qvw file, reload was successful.

Is that the correct location for that file?

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Emmanuelle__Bustos — Wed, 06 Apr 2016 18:04:56 GMT

Hi Nishiant did you solve this issue? I'm having the same problem.

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Wed, 06 Apr 2016 18:12:03 GMT

Hi emmanuelle.bustos

I just copied the tnsnames.ora file to the same folder where I had my qvw saved. That solved the issue and reload was successful. I don't know if that is the right folder to save tnsnames.ora file though.

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Wed, 06 Apr 2016 18:12:48 GMT

Hi emmanuelle.bustos

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Emmanuelle__Bustos — Thu, 07 Apr 2016 16:42:18 GMT

Hi, It is weird as we isntall client components for Oracle 11g the tnsnames.ora is not present at the Windows directoris so we cannot move to the folder as you mentioned, we are still looking for a solution.

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Anonymous — Thu, 07 Apr 2016 16:55:05 GMT

The Oracle Client install just installs the client binaries but needs to be told where the database resides. This is done by manually created a suitable tnsnames.ora file. Your dba should already have a suitable tnsnames.ora file [not least on the Oracle Database Server itself], so ask them to copy it into the correct folder within your Oracle Client folder.

Putting a tnsnames.ora file into the same folder as your qvw may well work, but is the wrong thing to do and will most likely cause confusion later as well as upsetting your dba's when it goes wrong and they have to fix it.

In summary: Ask your dba for assistance.

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Emmanuelle__Bustos — Thu, 07 Apr 2016 17:33:28 GMT

Thank you Bill for the tip, we are looking for the DBA admin help.

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Emmanuelle__Bustos — Thu, 07 Apr 2016 18:20:27 GMT

Hi while struggling with this I found another workaround that I think is worth ti use, this consists in using a full description DB in the connection string like this:

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;user id=username;password=pass;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<ip_host>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service_name)))];

and for the problem showing the password we can put this connection string into the Hidden Script.

I think is a good workaround while trying to configure the listener.ora and tsnnames.ora files.

Regards!

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

Emmanuelle__Bustos — Tue, 12 Apr 2016 16:55:43 GMT

Hi, with my last post where i used a forced string like this: OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=False;user id=username;password=pass;data source=(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<ip_host>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service_name)))]

We were still having problems.

So struggling a litlle bit more we finally found out the correct configuration to let this thing working as follows:

1. Having the correct driver installed in the machine, find the tnsnames.ora file in the Oracle client directory on my computer was --> C:\app\Soporte\product\11.2.0\client_1\Network\Admin\Sample.

2. Edit with notepad the tnsnames.ora file putting the string for the SID corrsponding to your DB this must be provided by your Oracle DBA once it is configured in DB server, in my case is:

BBG =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = <ip host>) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = BBG)

)

3. Create the TNS_ADMIN environment variable setting the value with the tnsnames.ora directory, on my machine was --> C:\app\Soporte\product\11.2.0\client_1\Network\Admin\Sample

4. Restart your machine.

5. Open the qvw and the script editor, click on Connet to OLE DB button.

6. Select the Oracle Provider for OLE DB driver.

7. In data source field input:

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<iphost>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<SID>)))

*SID in my case is BBG, check step 2 for reference.

8. Input the userid and password.

9. Click Test Connection button and verify that it is correct.

10. Click Accept button.

11. A little window must show up "OraOLEDB Logon", input the password for the User ID again and so the connection string is created.

12. Verify the connecton string is created at the script.

13. Create the script for the extraction (now we can use the Select button at the bottom to display the Wizard of extraction BD).

*NOTE: If you use the "Select tables Wizard", you must select the correct user in the Owner field in order to view the tables to which it have access (anonymous user is default).

Hope this help other users as it is working for us.

Best Regards!

Re: listener was not given the SERVICE_NAME in CONNECT_DATA

srchilukoori — Tue, 31 Oct 2017 21:46:10 GMT

Creating the 'TNS_ADMIN' environment variable resolved the error.

Thank you