Write Data In-DB temporary Table.

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?

Datasets

Accepted answers

All comments

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!

davidhenington

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-Table-Security/idi-p/156939

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....

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703