SAP Knowledge Base Article - Public

2770327 - Error:'incorrect syntax near "Insert" ...' when trying to add a command object to Crystal Reports

Symptom

  • Unable to add custom SQL that uses temp table.
  • Error when trying to add a command object that use a temporary table in Crystal Reports.
  • When reporting off HANA, and trying to add a custom SQL in a command object that includes a temp table, Crystal Reports fails with the error:
              
       "Failed to retreive data from the database.
        Details: 42000:[SAP AG][LIBODBCHDB32 DLL][HDBODBC32] Syntax
        error or access violation:257 sql syntax error: incorrect syntax near 
        "Insert" [Database Vendor Code: 257 ]"
        
      
       INSERT_ERROR.png

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016
       
  • HANA 1.0
  • HANA 2.0 

Reproducing the Issue

  1. In Microsoft ODBC Administrator, create an ODBC DSN to HANA
       
  2. In Crystal Reports, create a new report off HANA data source using an ODBC connection.
       
  3. Click on "Add Command", and add your own SQL in which it creates a temp table, and insert data into it like:
       
       Create Table #myTempTable (myTempField Varchar(50))
       Insert Into #myTempTable Select MyField From MyTable
       Select myTempField From #myTempTable
       
  4. When clicking "OK", it generates the error:
           
       "Failed to retreive data from the database.
        Details: 42000:[SAP AG][LIBODBCHDB32 DLL][HDBODBC32] Syntax
        error or access violation:257 sql syntax error: incorrect syntax near
        "Insert" [Database Vendor Code: 257 ]"

Cause

  • The error occurs because temp table is not supported in a Command Object in Crystal Reports.
               
  • In more details, Crystal Reports supports SQL Query Language in a Command Table 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   
            
  • This is because Crystal Reports is a reporting tool, and therefore open a read only connection to the data source.

Resolution

  • Modify the SQL Query to use SQL Query Language supported by Crystal Reports to retreive data.

Keywords

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

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016