4 Replies Latest reply on Oct 17, 2017 7:40 PM by Charles M

    nqserver log  - syntax errors

    Charles M

      Hi All -

       

      I was looking through our nqserver log recently, and noticed that there were quite a few "syntax" errors in it. I can see that the all seem to be for the same thing. Here is an example:

      [2017-10-17T09:18:58.322-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 005MvrrE6v99Tc85vjl3iX0007TX000Ro1,0:1:1:13] [tid: 1deb6700]  [nQSError: 27002] Near <)>: Syntax error [nQSError: 26012] . [[^M
      file: server/Metadata/NQSParser/Src/NQSPScanner.cpp; line: 2321^M

      **********  Task: 1. Running for (mls): 0 **********
      Description: Execute logical request
      User: {user_id}
      SessionID: 1161428992
      ProcedureID: 113
      SQL Hash: 0x989ee0fc
      SQL::{call SAGetSharedRequestKey('SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name")')}
      ^M
      ]]

       

      I'm trying to find out exactly what this means, and if we should be concerned with it. The consultants who performed this work are no longer here, but I can retrieve what is necessary to help troubleshoot.

       

      Thanks in advance ...

       

      Regards,

       

      Charles

       

      OBIA 11.1.1.10.1 / OBIEE 11.1.1.9.3

        • 1. Re: nqserver log  - syntax errors
          Sherry George

          It might be a bug. Find out what object produces the SQL in the error message and see if it is producing any error in front end or try to modify the logic and see if the same error is appearing in the log.

          SQL::{call SAGetSharedRequestKey('SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name")')}

           

          From doc.

           

          Sharing the Presentation Services Query Cache

          When users access Answers to run queries, Presentation Services caches the results of the queries. Presentation Services uses the request key and the logical SQL string to determine if subsequent queries can use cached results. If the cache can be shared, then subsequent queries are not stored.

           

          SAGetSharedRequestKey: An ODBC procedure that takes a logical SQL statement from Presentation Services and returns a request key value.

          The following shows the syntax of this procedure:

          SAGetSharedRequestKey('sql-string-literal') 
          1 person found this helpful
          • 2. Re: nqserver log  - syntax errors
            Charles M

            Hi Sherry,

             

            Thank for the reply. Some good info there. I ran an analysis with that subject area object, but didn't get any error from the front end:

            Here is the 'logical SQL' I found for it (using Fusion Applications: How to set up and access the nqquery.log to troubleshoot a single OTBI or OBIA query (Doc ID 1469168.1)):

            SELECT

              0 s_0,

              "Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name" s_1

            FROM "Financials - AP Transactions"

            ORDER BY 1, 2 ASC NULLS LAST

            FETCH FIRST 500001 ROWS ONLY

             

            I can see the errors in the nqserver log, as follows (some other entries there ... I'm not sure if they are completely relevant here, but posting all for context):

            [2017-10-17T13:56:18.933-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: bb3727d68120b54d:270c056:15f2ac45751:-8000-0000000000001752,0:1:5:1] [tid: 57ca4700]  [nQSError: 17014] Could not connect to Oracle database. [[^M
            file: server/Query/Execution/DbGateway/Oci10g/../Oci8/Src/SQXDGOci8.cpp; line: 373^M
            Properties: description=<<1057772>> DbGateway Exchange: Oracle BI Application Configuration.Oracle BI Application Configuration Connection Pool; producerID=139664722025384; requestID=3011182596; sessionID=3011182592; userName={USER}; ^M
            [nQSError: 17001] Oracle Error code: 12154, message: ORA-12154: TNS:could not resolve the connect identifier specified
            at OCI call OCIServerAttach.^M

            **********  Task: 1. Running for (mls): 404 **********
            Description: DB Connect
            DSN: CM_DSN; userName=CM_USER

            **********  Task: 2. Running for (mls): 404 **********
            Description: <<1057736>>DbGateway Prepare
            DSN:Oracle BI Application Configuration Connection Pool;userName:CM_USER
            SQL:
            select D1.c3 as c1,
                 '' as c2,
                 D1.c2 as c3,
                 'column' as c4,
                 D1.c1 as c5
            from
                 (select distinct T2746832.MULTI_CURRENCY_SUPPORT_FLG as c1,
                           T2746833.CURR_DISPLAY_NAME as c2,
                           T2746832.CURR_PREFERENCE_KEY as c3
                      from
                           C_BIA_CURR_PREF_OFRNG_REL T2746830 /* Fact_C_BIA_CURR_PREF_OFRNG_REL */ ,
                           C_BIA_CURRENCY_PREFERENCE T2746832 /* Fact_C_BIA_CURRENCY_PREFERENCE */ ,
                           C_BIA_CURRENCY_PREFERENCE_TL T2746833 /* Fact_C_BIA_CURRENCY_PREFERENCE_TL */
                      where  ( T2746830.CURR_PREFERENCE_KEY = T2746832.CURR_PREFERENCE_KEY and T2746830.CURR_PREFERENCE_KEY = T2746833.CURR_PREFERENCE_KEY and T2746833.LANGUAGE_CODE = 'US' )
                 ) D1
            order by c3

            **********  Task: 3. Running for (mls): 404 **********
            Description: Producer::ExecuteOnce()
            Activity type:Producer::ExecuteOnce()
            Repository Name::Star Subject Area Name::Core User Name::{user}
            Logical Hash of SQL:: 0x44bfb7d0
            SQL::
                        SELECT "Dynamic Currency Preference"."Preference List"."Currency Preference Code", '', "Dynamic Currency Preference"."Preference List"."Currency Preference Name", 'column',"Dynamic Currency Preference"."Preference List"."Currency Support Flag" FROM "Dynamic Currency Preference"."Preference List" order by "Dynamic Currency Preference"."Preference List"."Currency Preference Name" ASC

            ^M
            ]]^M
            [2017-10-17T13:56:18.937-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: bb3727d68120b54d:270c056:15f2ac45751:-8000-0000000000001752,0:1:12] [tid: 3cb88700]  [nQSError: 27002] Near <)>: Syntax error [nQSError: 26012] . [[^M
            file: server/Metadata/NQSParser/Src/NQSPScanner.cpp; line: 2321^M

            **********  Task: 1. Running for (mls): 0 **********
            Description: Execute logical request
            User: {user}
            SessionID: 3011182592
            ProcedureID: 113
            SQL Hash: 0x989ee0fc
            SQL::{call SAGetSharedRequestKey('SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name")')}
            ^M
            ]]^M
            [2017-10-17T13:56:18.938-04:00] [OracleBIServerComponent] [ERROR:1] [] [] [ecid: bb3727d68120b54d:270c056:15f2ac45751:-8000-0000000000001752,0:1:16:3] [tid: 3cb88700]  [nQSError: 27002] Near <)>: Syntax error [nQSError: 26012] . [[^M
            file: server/Metadata/NQSParser/Src/NQSPScanner.cpp; line: 2321^M

            **********  Task: 1. Running for (mls): 0 **********
            Description: Prepare Logical Request
            User: {user}
            SessionID: 3011182592
            ProcedureID: -1
            SQL: SET VARIABLE QUERY_SRC_CD='DashboardPrompt';SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name")

             

            Regards,

             

            Charles

            • 3. Re: nqserver log  - syntax errors
              Gianni Ceresa

              Hi, in your log you have the following:

              SQL: SET VARIABLE QUERY_SRC_CD='DashboardPrompt';SELECT MIN("Financials - AP Transactions"."Payables Invoicing Business Unit"."Payables Invoicing Business Unit Name")

               

              Looks like the query generating the issue from a dashboard prompt object. One possible option is that you have a dashboard prompt which try to set a default value to the minimum of "Payables Invoicing Business Unit Name", which could match with the first value of the prompt (assuming it's the name of the BU). It's possible they did a mistake in the syntax of the "default value" LSQL and that's the source of the issue. Of course you don't really see it because the prompt still work and probably set the default value to the first one "by default" even if the query entered generate an error.

              1 person found this helpful
              • 4. Re: nqserver log  - syntax errors
                Charles M

                Hi Gianni,

                 

                You are correct. This is coming from our dashboards. We have 2 custom dashboards that I can see, which are using this prompt.

                 

                The first one(all 4 tabs) defaults to an "all column" value. Like this:

                The second one (4 of 5 tabs) default to that second value in the list. Like this:

                 

                The last tab is different. It does not have the "all columns" value, but still defaults to the second value in the list. Like this:

                 

                Regards,

                 

                Charles