We're excited to announce that we'll be partnering with Credly starting October 19th - see what this means and read the announcement blog here!

Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
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
15 - Aurora
15 - Aurora

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.

LisaL
Alteryx
Alteryx

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
Senior Customer Support Engineer -- Knowledge Management Coach
Alteryx, Inc.
vepp
8 - Asteroid

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

Labels