listener was not given the SERVICE_NAME in CONNECT_DATA

[#1]

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

[Emmanuelle__Bustos]

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!

[Emmanuelle__Bustos]

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!

[srchilukoori]

Creating the 'TNS_ADMIN' environment variable resolved the error.

Thank you