Powerpivot - Teradata Query - Error 3932

Third Party Software
Visitor

Powerpivot - Teradata Query - Error 3932

I am new to Teradata, so I am trying to learn all its quirks. What I am trying to accomplish is write a temp table and then query off of it all in one statement. When I run the query below in Powerpivot I get the following error:

[Teradata Database] [3932] Only an ET or null statement is legal after a DDL Statement.

If I run the same query in Teradata, it works if I use execute. If I use execute parallel, I get the same error. I am assuming that when it runs in parallel it is creating a circular reference. I am assuming the easiest solution would be to not run the query in parallel, but I don't see any way to accomplish this using Powerpivot. Any advise would be appreciated.

I miss using Microsoft SQL

CREATE VOLATILE TABLE CGCC
(
ID INT,
NAME VARCHAR(20)
)
ON COMMIT PRESERVE ROWS;

SELECT * FROM CGCC;
DROP TABLE CGCC

10 REPLIES
Apprentice

Re: Powerpivot - Teradata Query - Error 3932

Hi,

 

You've probably worked this out by now (or you're using Microsoft SQL Smiley Happy ), but for anyone else...

 

I'm guessing that this error code stems from the transaction mode being used by the PowerPivot connection to Teradata.

 

You'll probably find that PowerPivot is connecting a session to Teradata using ANSI transaction semantics. With this setting a transaction starts with the first SQL request and is not terminated unless you COMMIT, logoff or hit an error.

 

When a DDL request completes in an active transaction, the only acceptable other commands in that transaction are COMMIT, logoff or ABORT/ROLLBACK.

 

The other 'transaction semantics' option available to you is Teradata mode (aka BTET). By default in Teradata mode each request is a transaction, hence the 'CREATE TABLE' is automatically committed when it completes, terminating the transaction which then allows the SELECT request to run ok.

 

When you 'run it in Teradata' I assume that you mean using TD Studio or SQL Assistant. With either of those then 'by default' they will either auto-commit after each request if in ANSi mode or they will connect using Teradata mode.

 

In your Powerpivot connection settings/properties check for a parameter called TMODE or 'transaction mode' (or something similar). What it is called will depend on which API is being used (JDBC, ODBC, Net etc.).

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Tags (2)
Enthusiast

Re: Powerpivot - Teradata Query - Error 3932

Hi Nunz2,

 

Were you able to find a solution for this issue? I am facing the same issue.

 

The solution presented by DaveWellman does not solve it.

 

Thanks a lot and kind regards,

 

Fabiano

Apprentice

Re: Powerpivot - Teradata Query - Error 3932

Hi Fabiano,

 

Sorry to hear that, can you provide some more info about your processing?

 

Which transaction mode is your connection using? If not known, connect with Powerpivot and whilst logged on run the following:

SELECT USERNAME,sessionno,transaction_mode
FROM dbc.sessioninfov
WHERE USERNAME = '**bleep**';

In the above query replace "**bleep**" with your Teradata user name.

 

Can you provide the sql statements being run up to the point where this error occurs - if not too many.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Apprentice

Re: Powerpivot - Teradata Query - Error 3932

Also, are you connecting with ODBC, JDBC etc.?

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Powerpivot - Teradata Query - Error 3932

First of all, thanks for your reply.

 

I am connecting using Other Sources - > Other (OLEDB/ODBC) -> Microsoft OLE DB Provide for ODBC Drivers -> Teradata

 

I tried the following query on both session modes: ANSI and Teradata

 

create volatile table cpn11, no log as (
select
  lof_list.initial_tkt_num
  ,trim(cpn.coupon_orig_cd)||trim(cpn.coupon_dest_cd) as od11
  ,cpn.dprt_dt as dprt11
  ,lof_list.oper_carr_cd as oper_carr11
  ,lof_list.mktg_carr_cd as mktg_carr11 
  ,lof_list.tktng_cos as cos11
  ,lof_list.tkt_designator as des11
 
 from co_prod_vmdb.vw_tcn_all_lof lof_list
 
 left join  co_prod_vmdb.vw_agency_current_with_hol agy
 on agy.agency_id = lof_list.tktng_agency_id
 
 left join co_prod_vmdb.vw_tcn_coupon cpn
 on lof_list.record_id = cpn.record_id
 and lof_list.relative_tkt_seq = cpn.relative_tkt_seq
 and lof_list.coupon_num = cpn.coupon_num
  
 where
  lof_list.pos_country_cd in ('AR') and lof_list.tkt_stock_id = '016' and lof_list.relative_tkt_seq =1 and lof_list.coupon_num = 1 and lof_list.issued_dt between '2017-08-01' and '2017-08-01'
) with data primary index (initial_tkt_num) on commit preserve rows;


select * from cpn11

 

ERROR [HY000] [Teradata][ODBC Teradata Driver][Teradata Database] Only a COMMIT WORK or null statement is legal after a DDL Statement

 

Enthusiast

Re: Powerpivot - Teradata Query - Error 3932

ERROR [25000] [Teradata][ODBC Teradata Driver][Teradata Database] Only an ET or null statement is legal after a DDL Statement.

 

Error when using "Teradata" mode session.

Apprentice

Re: Powerpivot - Teradata Query - Error 3932

Hi,

 

From the dbms perspective:

In Teradata mode, the default is that every request is a separate transaction and is implicitly comitted by the dbms. You/your application don't have to do anything.

 

The above default can be over-ridden by using BT/ET commands which sets up an explicit transaction.

 

In ANSI mode there is only one choice which is: the first request starts the transaction and the transaction is only terminated by a Failure, COMMIT, ABORT/ROLLBACK or Logoff.

 

And then you find out what the api level drivers are doing...

It looks like the TD ODBC driver supports the SQL_AUTOCOMMIT parameter.

I'm guessing that when you hit this error in Teradata mode:

- SQL_AUTOCOMMIT is set to false,

- the ODBC driver (or PowerPivot) is sending a 'BT' command before your CREATE TABLE.

- the following SELECT is then part of the same transaction as the CREATE TABLE - which is not allowed.

(we can get similar things happening when using the Teradata JDBC driver)

 

There needs to be an "ET" command after the CREATE TABLE command.

 

In ANSI mode there needs to be a COMMIT between the CREATE TABLE and the SELECT.

 

You can prove what SQL commands are being received by Teradata by using Query Logging. Trace all sql requests for your the relevant userid, you don't particularly need any of the other options for this process.

 

Can you check the connection settings for your Powerpivot setup? Do you have an 'auto commit' option (or something similar)?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Powerpivot - Teradata Query - Error 3932

 

8-31-2017 12-07-16 PM.jpgThese are my connection settings.

Apprentice

Re: Powerpivot - Teradata Query - Error 3932

Thanks. Nothing obvious (to me !) in there.

 

Are you able to view the SQL sent to Teradata? As per my earlier post you can use 'Query Logging' to do that. If you don't control this area you may need to speak to a DBA to see what is available and/or get it turned on for the userid that you're using.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com