- 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 ]'"
- SAP Crystal Reports 2011
- SAP Crystal Reports 2013
- SAP Crystal Reports 2016
- SAP Crystal Reports 2020
- IBM Netezza
Reproducing the Issue
- In Crystal Reports, create a new report connecting to an IBM Netezza data source using an ODBC connection.
- In the "Database Expert" window, double click on "Add Command"
- In the "Add Command to Report" window, enter SQL that creates a temp table.
- When clicking "OK" to accept the SQL, it fails with an error.
- 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:
- GROUP BY
- ORDER BY
- But does not support SQL Query Language for data manipulation like:
- CREATE TABLE
- ALTER TABLE
- DROP TABLE
- Modify the SQL in the Command Object to use SQL Query Language supported by Crystal Reports to retreive data only.
CR, Temp Table , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem
SAP Crystal Reports 2011 ; SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020