Inspire EMEA 2022 On-Demand is live! Watch now, and be sure to save the date for Inspire 2023 in Las Vegas next May.

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer and Intelligence Suite.

Data Stream In and Write Data In-DB

galex
6 - Meteoroid

I need to create a temporary table while running an in-database  query to avoid a spool space error. I connected to the database, did some manipulation, then tried outputting to a table using "Data Stream In" tool with Creation  Mode=Create Temporary Table, but I got this error "Error: Data Stream In (113): Error SQLDriverConnect: [Teradata][ODBC Teradata Driver][Teradata Database] The UserId, Password or Account is invalid. [Teradata][ODBC Teradata Driver] Not enough information to log on". However, I connected in the "Data  Stream In" tool using the same connection as in the "Connect In-DB" tool and the latter worked fine. What should I be doing differently? 

 

I also tried using  the "Write Data In-DB" tool, but get the same error. With this tool, I'm not sure how to even tell it where to connect.

 

Thanks in advance!

 

8 REPLIES 8
patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@galex

Have you confirmed with your Teradata DBA that you have the appropriate permissions?

galex
6 - Meteoroid

Hi. Thanks. The problem wasn't with permissions. Actually, someone else pointed out that the read and write configurations have to be the same and I had them slightly different. That fixed the problem with the Data Stream Out.

Thanks.

DavidAngevine
7 - Meteor

I have this error:

Error: Data Stream In (1): Error creating table "AYX18042653b3526e4391ed1b13": [Teradata][ODBC Teradata Driver][Teradata Database] The user does not have CREATE TABLE access to database CDW.
CREATE MULTISET TABLE "AYX18042653b3526e4391ed1b13" ("Provider_id" varchar(255) CHARACTER SET UNICODE,"NPI" varchar(255) CHARACTER SET UNICODE,"TAX_ID" varchar(255) CHARACTER SET UNICODE,"Name Provider/Group" varchar(255) CHARACTER SET UNICODE,"Entered Date" date,"Flag Start Date" date,"Flag End Date" date).  

I checked the rights for my userid and it can write to a volatile or temp table in Teradata when I use the native environment.  

I also checked the Input / Output rights and both are the same with the same ODBC connection (EDW_PROD) that is on our server.  

Any suggestions?

patrick_mcauliffe
14 - Magnetar
14 - Magnetar

@DavidAngevine you need to have the DBA grant you write access that includes table creation.

DavidAngevine
7 - Meteor

@patrick_mcauliffe I do have write access to Teradata, and can already write to the database with the direct SQL method.   I cannot do it with Alteryx.   Somehow, this was an issue but it was solved after updating the connection In-Database.  There were multiple user_ids and I changed to use only 1 user_id in the entire workflow.

Eric_M
5 - Atom

I Just noticed this issue with the 2018.2 upgrade. I used to be able to have the Teradata Bulk loader for the Write tab with extra amps allocated for large data. It can still work if you past in the tdbl string in the Odbc read tab but I'm not sure that's a good work around. It only seems to be a problem for the In DB but at least the stream in works again.

 

Thanks for the tip!

ak2018
8 - Asteroid

hi, I know this is an old post, but if you do remember, I need your help. I am able to push data into a temp table in TD but how to you do the next step of running a query that uses that data with a truckload of other tables to output? I could not find a tool that will run a query in-db except the Connect In-DB tool which does not take an input, making the entire thing totally weird for me. 

galextny
5 - Atom

If all your tables are in Teradata, you could create a temp table in the SQL and do all joins there. If the other tables are in other databases, the only solution I can think of is to use an in-db Data Stream Out tool and a join tool to connect to them. If your goal in using temp tables is to be more efficient, that won't help, though.

Labels