SAP Knowledge Base Article - Public

2395374 - Error: 'HY000:ERROR: CREATE TABLE AS SELECT not permitted in read-only session' when attempting to create a report using temp table in Crystal Reports

Symptom

  • Unable to create a new report off a Command Object.
  • When attempting to create a report using a custom SQL Query it fails.
  • When attempting to create a new report connecting to Netezza using an ODBC connection in Crystal Reports, and entering in a Command Object an SQL Query that uses a temp table, it fails with the error:
          
    "Database Connector Error: 'HY000:ERROR: CREATE TABLE AS SELECT not permitted in read-only session [Database Vendor Code: 46 ]'"

Environment

  • SAP Crystal Reports 2008
  • SAP Crystal Reports 2011
  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
        
  • IBM Netezza

Reproducing the Issue

  1. In Crystal Reports, create a new report connecting to an IBM Netezza data source using an ODBC connection.
  2. In the "Database Expert" window, double click on "Add Command"
  3. In the "Add Command to Report" window, enter SQL that creates a temp table.
  4. When clicking "OK" to accept the SQL, it fails with an error.

Cause

  • Crystal Reports does not support SQL that creates temp table in a Command Object, regardless of the data source used.
                
  • The reason Crystal Reports does not support creating temp table, it's because Crystal Reports is a reporting tool and only needs to read data from the data source, therefore when connecting to a data source using an ODBC connection, it opens a Read Only connection. Since the connection is read only, an error will occurs because the SQL sent tries to create change on the data source by creating a temp table, which requires a Read-Write connection.
               
  • Crystal Reports supports SQL Query Language in a Command Object to retreive data from a data source like:
    • SELECT
    • FROM
    • WHERE
    • GROUP BY
    • ORDER BY
    • UNION
                     
  • But does not support SQL Query Language for data manipulation like:
    • CREATE TABLE
    • ALTER TABLE
    • DROP TABLE
    • INSERT
    • UPDATE
    • DELETE

Resolution

  • Modify the SQL in the Command Object to use SQL Query Language supported by Crystal Reports to retreive data only.

Keywords

CR, Temp Table , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

Product

Crystal Reports 2008 V1 ; SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016