How to expand Dataprovider SQL Query column in 360Eyes database

How to expand Dataprovider SQL Query column in 360Eyes database

Symptom

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

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 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)

Keywords

database, SQL Server, Oracle

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

      SymptomInstalling 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: Suitable driver not found" when configuring 360Suite databases

      Symptom When configuring 360Suite main database When configuring 360Eyes database When trying to create a 360Cast or 360WOFBO SQL source When trying to create a 360View SQL Query Once you selected your database type and validate the form, you fet ...