8 Replies Latest reply: Nov 12, 2012 1:14 PM by Michael Tarallo RSS

    Expressor Oracle-connection problem

      Hi expressor community,

       

      I'm currently working on an expressor application where i have to read data out of an oracle database with an SID.

      In my Project i succesfully established and tested  the database-connection, but when i now want to extract some data following exception shows up:

       

      dataflow controller - ETASK-0017-N: Using Desktop license for QlikView Expressor Processor; resource utilization limited. (etask.controller)

      expressor dataflow execution utility (etask) 3.8.0.23255 n (Build 23255) Oct 13 2012

      <task dataflow="Read_Netport_Database">

      <!-- project.home[(null)], project[..] -->

      <step id="1" step_name="Step_1" process="3416" run="0" status="ok" start="2012-11-09T09:39:57">

      SQL Query 1 - OPERATOR-0050-A: toolId 6.1, name 'SQL Query 1' - Exception 'SQLSTATE:[HY000], Code:[0], Msg:[QVExpressor][ODBC Oracle Wire Protocol driver]Multiple terms for an Oracle server have been supplied in the connection information and is therefore ambiguous.  Please, supply only SID or Service Name in the connection information.

      ' occurred in the 'initialize' function. (Read_Netport_Database.Step_1)

      SQL Query 1 - OPERATOR-0003-F: toolId 6.1, name 'SQL Query 1' - the 'initialize' function failed. (Read_Netport_Database.Step_1)

      SQL Query 1 - ETOOL-0006-F: operator 6.1, type in-table, name 'SQL Query 1': etool failed, phase runInitialize. (Read_Netport_Database.Step_1)

      SQL Query 1 - ETOOL-0016-A: operator 6.1, type in-table, pid 3352 - initialize() failed. (Read_Netport_Database.Step_1)

        <status>failed</status>

      </step>

      <statistic>0.052</statistic>

      </task>

      dataflow controller - ETASK-0038-F: Processing has completed abnormally. (Read_Netport_Database.controller)

       

      I hope you can help me with this problem.

       

      Thomas

        • Re: Expressor Oracle-connection problem
          Michael Tarallo

          Hello Thomas -

           

          This error is coming from the Oracle Driver and not QlikView Expressor.

           

          Can you check the tnsnames.ora file to see how your SID / Service name is defined?

           

          I assume it is something that has more than a single value, something like yyyy.xxxx.

           

          Is that correct?

           

          If so - can you change your Service name to use a single value and test and let us know?

           

          After you check and verify - I can report this to engineering to see if this is by design.

           

          The file should be located here: C:\oraclexe\app\oracle\product\11.2.0\server\network\ADMIN

           

          Here is how mine is defined - note the XE

           

          XE =

            (DESCRIPTION =

              (ADDRESS = (PROTOCOL = TCP)(HOST = mtarallo1234)(PORT = 1521))

              (CONNECT_DATA =

                (SERVER = DEDICATED)

                (SERVICE_NAME = XE)

              )

            )

           

          oracle.PNG

           

          Let me know.

           

          Regards,

           

          Mike T

            • Re: Expressor Oracle-connection problem

              Hi Michael,

               

              thanks for your quick reply.

               

              The SID of this server is a single value 'asconfg' (this value is the on in the tnsnames.ora says our DB-Admin), I put under advanced options in the database-connection interface

               

              Name:          Value:

              SID               asconfg

               

              while I leave the 'Service Name' row empty.

               

              When i now qlik Edit -> Test Connection in the Connection Tab the massage

               

              'the connection test was successful'

               

              pops up. But when i now want to start a dataflow using this connection the errormassage from my OP is shown.

               

              Thomas

               

               

              PS: The same errormassage is also shown when I test the database-connection with any value (even Space) in the 'Service Name' row.

                • Re: Expressor Oracle-connection problem
                  Michael Tarallo

                  Hi Thomas - I reproduced the issue by configuring the Oracle Database connection the same way as you:

                   

                  ora2.PNG

                   

                  It did test successfully - however when running I got the same error as you:

                   

                  Please configure the connection like this (screen shot) and let me know if this solves your problem:

                   

                  oracle.PNG

                   

                  This solved mine.

                   

                  Please let me know.

                   

                  Regards,

                   

                  Mike T

                    • Re: Expressor Oracle-connection problem

                      Hi Michael,

                       

                       

                      No this does not solve my problem.

                       

                      when is insert 'asconfg' into Service Name, remove SID and test the Connection following error is returned:

                       

                      SQLSTATE:[08001], Code:[-1], Msg:[QVExpressor][ODBC Oracle Wire Protocol driver][Oracle]TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor

                       

                      Hope this helps

                       

                      Thomas

                       

                      PS: the tnsnames.ora looks like this:

                       

                      DB Name=

                        (DESCRIPTION =

                          (ADDRESS_LIST =

                            (ADDRESS = (PROTOCOL = TCP)(HOST = DB Host)(PORT = 1521))

                          )

                          (CONNECT_DATA =

                            (SID = asconfg)

                          )

                        )

                        • Re: Expressor Oracle-connection problem
                          Michael Tarallo

                          Hello Thomas

                           

                          Here is what I got from our engineering team:

                           

                          The service name in our connection box corresponds to the TNS service name, make sure the user verified the defined TNS service name and the port number from his DBA so he can use that name/port to connect. The SID attribute is meaningless in the datadirect driver manager, it is simply ignored in the ODBC connection string.

                           

                          Please verify and let me know.

                           

                          Mike T

                            • Re: Expressor Oracle-connection problem

                              Hello Michael,

                               

                              I tested as you considered above and it also wasn't working.

                               

                              I got these data for the connection in my OBDC-Admin Tool:

                               

                              Expressor_DB-Connection.png

                               

                              And these in the Expressor DB-Connection:

                               

                              Expressor_DB-Connection2.png

                               

                              But I always get this error message, when I test the above connection:

                               

                              Expressor_DB-Connection_Error.png

                               

                              So I don't know what the problem could be, but I hope these information's help.

                               

                              Thomas

                               

                              PS: I tested the connections settings with the SID again and noticed that I am able to create an DB-Schema with the connection and get the correct table-information's out of the database.

                              So the error occurs only when I run a dataflow reading the actual data in the DB.

                                • Re: Expressor Oracle-connection problem
                                  Michael Tarallo

                                  Hi Thomas - I have engineering looking into this as well - in the mean-time - can you attempt to get the DBA to create or edit a connection in the the same format in the tnsnamesora that I have such as:

                                   

                                  XE =

                                    (DESCRIPTION =

                                      (ADDRESS = (PROTOCOL = TCP)(HOST = mtarallo1234)(PORT = 1521))

                                      (CONNECT_DATA =

                                        (SERVER = DEDICATED)

                                        (SERVICE_NAME = XE)

                                      )

                                    )

                                   

                                  here is yours:

                                   

                                  DB Name=

                                    (DESCRIPTION =

                                      (ADDRESS_LIST =

                                        (ADDRESS = (PROTOCOL = TCP)(HOST = DB Host)(PORT = 1521))

                                      )

                                      (CONNECT_DATA =

                                        (SID = asconfg)

                                      )

                                    )

                                   

                                  I'd like to do this just to be sure. What you are experiencing is not consistent any way. If the connection tests successfully and you can create a schema, the problem may be in the Read Table operator.

                                   

                                  I will run some tests on this side and please note I did send engineering your update.

                                   

                                  Regards,

                                   

                                  Mike T

                                  • Re: Expressor Oracle-connection problem
                                    Michael Tarallo

                                    Thomas - can you send your workspace directory zipped up and attach to the discussion?

                                     

                                    It will be whereever you decided to create your Workspace such as:

                                     

                                    C:\<path>\<workspaces>\<workspace>

                                     

                                    Thanks