Run a DDL statement before query getting data

[serda]

Hi everybody!

I am trying to retrieve data directly against Teradata with the import mode and writing the SQL query in the optional textbox. Teradata raises an error because it can't run a DDL statement with a DML statement in the same transaction.

It could be nice to separate DDL and DML statements in different sessions/connections from Power BI to databases.

In my case, I need to run this DDL statement in order to have access to several tables:

SET ROLE sampleRole;

If in the query textbox I try the following:

SET ROLE sampleRole;

A query;

The below error is raised:

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

I have tried to embrace the DDL statement in a transaction but the same error is raised:

BT;

SET ROLE sampleRole;

ET;

A query;

Does anyone comes up with anything?

Thanks in advance!

[v-qiuyu-msft]

Hi @serda,

As far as I know, DDL is used to alter/modify a database or table structure and schema. While DML affects row records in a table. In your scenario, I guess the issue is that it's not supported to use DDL statement when you connect to Teradata database. Please try to write the DML statement like SELECT instead of DDL.

Best Regards,
Qiuyun Yu

[serda]

I have to run a DDL statement in order to have permissions to SELECT a table, I try the following statements:

SET ROLE theRoleIneed;

SELECT * FROM theTable;

But, Teradata do not let you write a DDL statement with a DML statement in the same transaction, my question is if there is a way to avoid this, for example, Power BI could perform two different sessions/conections independently. Or maybe with BTEQ you can avoid this although I tried with this excerpt:

BT;

SET ROLE theRoleIneed;

ET;

BT;

SELECT * FROM theTable;

ET;

Thank you for your try, I did not explain it well.

[v-qiuyu-msft · Accepted solution]

Hi @serda,

As the error message stated, it's not supported to use DDL and DML at the same time when connecting to Teradata database in desktop. In my opinion, SELECT statement will return records based on the entered credential which used to connect to Taradata database. So we can just set permission for this account on Teradata database side, and write SELECT statement in the query box.

In your scenario, please set proper permission for the account used to access Teradata database. Only write "SELECT * FROM the Table" in desktop query box.

Best Regards,
Qiuyun Yu