Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

primary Key required for Update option - Oracle ODBC but Primary Keys do exist

vepp
8 - Asteroid

All,

 

Primary Key is available in Database (Oracle Cloud) But Alteryx ODBC Output data unable to detect. Can somebody please help?

 

Error Message :   Output Data (23) Primary Key required for Update option

 

 

 

ODBC Settings :

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Field Mappings :

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

key Defined in Database : 

 

Capture.PNG

 

 

 

 

 

 

 

 

 

 

Options Tried :

 

Dropped and recreated PK with different names - No success

Tried Append Data in Output and it worked fine with inserting records so Column name is not an issue

Dropped and recreated table with constraint ,didn't work

Changed None/Quoted in SQL style, didn't work

 

 

I have seen couple of posts related to the same issue but none of the solution worked for me. My source is flat file and  target is Oracle Cloud. I would really appreciate if someone shares their idea.

 

5 REPLIES 5
joshuaburkhow
ACE Emeritus
ACE Emeritus

Did you also try via OleDB instead of ODBC per this article or using the preSQL statement?

https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Primary-Key-Required-for-Update-Option...

Screen Shot 2019-09-23 at 1.35.34 PM.png

Joshua Burkhow - Alteryx Ace | Global Alteryx Architect @PwC | Blogger @ AlterTricks
vepp
8 - Asteroid

Nope, none of the option worked. I tried Both.

vepp
8 - Asteroid

Just for others who look for solution :

 

odbc:DSN=COnnectionName|||SchemaName.TableName -> This is required for Updates/Deletes

odbc:DSN=COnnectionName|||TableName -> Insert was working without schema name

 

Even though we have hard coded the schema name in ConnectionName, it does not pickup for Updates. The error was also misleading.  I am sure its a bug in Alteryx. It should not work differently for different type.

lepome
Alteryx Alumni (Retired)

Just for a little flavor, I'm seeing that error when I do specify the schema.  What makes the difference on my instances is that I have to use all lowercase table name for the Update to work (despite creating the table all uppercase).

Lisa LePome
Principal Support Engineer -- Knowledge Management Coach
Alteryx, Inc.
vepp
8 - Asteroid

@lepome  Interesting,  which DB it is?  Mine was oracle.

Labels