- Custom SQL calling Teradata volatile tables are failing with database error in Crystal Reports.
- Unable to use "Command Object" calling Teradata Volatile tables in Crystal Report. Same SQL is working when directly executed at database end.
- Crystal Reports 2008
Reproducing the Issue
Create a new connection in Crystal Reports connecting to Teradata database.
- Select "Add Command" option and add the code to call volatile table. (Sample command is given below)
- Click OK
- Crystal reports would display "Failed to retrieve data....[Database Vendor Code:-3932]" error
Create volatile table test_vt
( Field_1 integer,Field_2 integer)
primary index (Field1,Field2)
on commit preserve rows
insert into test_vt
Select * from test_vt
In order to query from volatile table is requires DDL (create table)followed by DML (Insert). If the code is submitted in one request to the Teradata, it will run as an implicit transaction and as you cannot run DML after the DDL, query would fail.
This is default behavior from Crystal Reports designer as Crystal Reports do not handle Volatile tables differently.
Instead of using Volatile table create a global temporary table in the database. Similar to Volatile tables, its space would come from temp space.It would require create table command to be run only once at database end after which we can simply call DML(Insert statement) from Crystal Reports without requiring DDL to be included in the code.
Create a stored procedure to execute the custom code calling volatile table. Then create reports on stored procedure.
DDL, DML, Teardata, Volatile tables. , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem