Write Data In-DB temporary Table.

[mariop]

So I'm using the Write Data In-DB and under the creation mode selecting Create Temporary Table. When I excute the workflow it all goes well until it tries to create the temporary table and I get an error message:Error: Write Data In-DB (5): Error running PreSQL on "NoTable": [Teradata][ODBC Teradata Driver][Teradata Database] The user does not have CREATE TABLE access to database xxxxxx.

This is correct, i dont have access to the xxxxxx database and would like to change where the temporary table is being created. Is there a way to change what database this temporary table is created on?

[MikeA]

Hi @mariop,

Alteryx does not manage the temporary tables in a database and these are set up by your DBA or person that administers the database; Alteryx can only leverage these tables once they have been set up in the database itself. There is not a way to change where a temporary table is being created from Alteryx. 

If you are able to however, you could create a new table in the Write Data In-DB tool to a location that you have access to:

Thanks!

[Jash]

Hi -

I have a similar problem. I DO have access to create a temp table in our Teradata database.

The SQL to create temp tables in Teradata is "Create MULTISET VOLATILE TABLE Temp_tablename...."

I tried executing this in a SQL Assistant and it worked perfectly.

However, Alteryx generates the SQL as "CREATE  MULTISET  TABLE "AYX17040533fe5552f0477a8605" ..." missing the "VOLATILE" word in the statement. I do not have access to create permanent tables, and hence my workflow fails. Error message is noted below.

Data Stream In (14) Error creating table "AYX17040533fe5552f0477a8605": [Teradata][ODBC Teradata Driver][Teradata Database] The user does not have CREATE TABLE access to database PRD_CONTR_DMV. ¶CREATE  MULTISET  TABLE "AYX17040533fe5552f0477a8605" ..... 

How do I fix this issue? Can the "VOLATILE" syntax be added to the Alteryx generated SQL statement when temp table option is chosen in the In-DB Data Stream In tool? Please help!

[HenrietteH]

Hi @Jash and @mariop

For the in-db tools Alteryx creates two separate sessions for read and write. The stream in tool first writes data in one session and then reads it in in another session. If it created a volatile table in the write session, the read session would not have access to it, which is why it doesn't create volatile tables. 

This post by another community user has a macro attached that creates a SELECT * FROM VALUES query for the dynamic input in-db tool that allows him to bring data into in-db without writing a table. 

You might be able to adapt his macro for your needs (NOTE: The dynamic input in-db tool does not accept CREATE TABLE statements, only SELECT statements). 

[simonaubert_bd]

Hello,

The main issue is that you can't choose where you create the temporary table, leading either to not using the function or safety issues. there is an idea to choose the temporary tables path:

https://community.alteryx.com/t5/Alteryx-Designer-Ideas/In-DB-Choose-the-base-or-schema-of-the-Temp-...

 Best regards,

Simon

[apathetichell]

@simonaubert_bd isn't this DB specific and at times controlled by the schema setting in the ODBC 64 config? For example - with Snowflake, if I enter a DB and Schema in ODBC 64 doesn't that carry over to my temp table? I haven't checked this in months but that is my vague memory.

[simonaubert_bd]

@apathetichellDepending on the database, you can set one in your dsn (or not). But if you want to have several places (because you work for several entities), you will need several dsn, several alteryx connection etc.

An other way we can't use in alteryx is to pass this parameter through session parameters (such as set something), since we can't use pre-sql on in database.

[apathetichell]

This is all part of my proposed "SQL" tool....