How to expand Dataprovider SQL Query column in 360Eyes database

How to expand Dataprovider SQL Query column in 360Eyes database

Symptom

​When running 360Eyes Impact Analysis Reports (such as Report 52), users observe that the SQL query stored in the 360eyes_REPORTS.unx / Eye Document / Dataprovider / Dataprovider Sql Queryfield is incomplete, partial or truncated. This often results in search operations failing to locate expected documents because the full SQL statement is not stored or displayed.

Typical user cases include searching for specific SQL patterns or keywords in reports, but results are missing due to this field size limitation.

In your 360Eyes Webi and Crystal job logs you are seeing data being truncated in EYE_DATAPROVIDER.DATAPROVIDER_SQL_QUERY field.

Environment

  • 360Eyes
  • SAP BusinessObjects Enterprise XI 3.1
  • SAP BusinessObjects Business Intelligence 4.0
  • SAP BusinessObjects Business Intelligence 4.1
  • SAP BusinessObjects Business Intelligence 4.2
  • SAP BusinessObjects Business Intelligence 4.3

Reproducing the issue

In the 360Eyes Webi or Cyrstal job log you will see entries similar to this:

 2018-10-02 11:51:32 -0400 INFO Value [SELECT   ACTG_DIM.ACTG_...] has been truncated to 4000 characters but has 11526 characters in total (field EYE_DATAPROVIDER.DATAPROVIDER_SQL_QUERY)

Cause

By default, the column allows for 4000 characters, which may not be enough to store the entire SQL statement from the dataprovider in your report(s).

Resolution 

Follow the steps below, for your specific database type, to increase the column length.

SQL Server

      ALTER TABLE EYE_DATAPROVIDER ALTER COLUMN DATAPROVIDER_SQL_QUERY VARCHAR(MAX) 

Oracle

Option #1: For oracle 12c and higher, limited to 32k and some db parameters :  http://www.oracletutorial.com/oracle-basics/oracle-varchar2/

Increase to a specific length:

       ALTER TABLE EYE_DATAPROVIDER MODIFY (DATAPROVIDER_SQL_QUERY VARCHAR2(#####));   enter specific length for #####

Option #2: Increase column length and to CLOB datatype:

      ALTER TABLE EYE_DATAPROVIDER ADD (DATAPROVIDER_SQL_QUERY_BIS CLOB);

      UPDATE EYE_DATAPROVIDER SET DATAPROVIDER_SQL_QUERY_BIS=DATAPROVIDER_SQL_QUERY;

      ALTER TABLE EYE_DATAPROVIDER DROP COLUMN DATAPROVIDER_SQL_QUERY;

      ALTER TABLE EYE_DATAPROVIDER RENAME COLUMN DATAPROVIDER_SQL_QUERY_BIS TO DATAPROVIDER_SQL_QUERY;

Update Universe - for Oracle #2 Option
The following object in the 360Eyes_REPORTS Universe will need to be updated in the Universe. The datatype for this object will need to be changed to LONG type if using option #2 for Oracle databases: 
      - Dataprovider Sql Query (in the Dataprovider Class)

Please run the next Webi or Crystal job in FULL mode to extract again the SQL queries for all reports.

Keywords

database, SQL Server, Oracle, truncated, partial

Product

Product or Product version

  • 360Eyes

Languages

  • English

    • Related Articles

    • Recommendation for using Oracle User name in 360Eyes

      Symptom Using 360Eyes as Oracle user name for the user/schema. Environment 360Eyes SAP BusinessObjects Enterprise XI 3.1 SAP BusinessObjects Business Intelligence 4.0 SAP BusinessObjects Business Intelligence 4.1 SAP BusinessObjects Business ...

    • How to install 360Eyes on SQL Server on Azure

      Symptom Installing 360Eyes using SQL Server on Azure. Environment 360Eyes SAP BusinessObjects Enterprise XI 3.1 SAP BusinessObjects Business Intelligence 4.0 SAP BusinessObjects Business Intelligence 4.1 SAP BusinessObjects Business Intelligence 4.2 ...

    • Error : "java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded"

      Symptom When running a 360eyes job (from the CMC or the GUI), the following error is received: "The program failed to run and supplied the following information: com.gbs.a.h: Error during Database update/migration: com.gbs.a.h: Error during ...

    • How to configure SQL Server with Windows Authentication

      Symptom When trying to configure a SQL Server database using Windows Authentication as part of the 360eyes database configuration, the following error is returned: "The driver is not configured for integrated authentication". Environment 360Eyes ...

    • "DB003: No suitable driver found" when configuring 360Suite databases

      Symptoms When configuring 360Suite main database. When configuring 360Eyes database. When trying to create a 360Cast SQL source. When trying to create a 360View SQL Query. In any of these situations, when the database type was selected, the following ...