community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

Primary Key Required for Update Option but Primary Key Exists - SQL Server

Asteroid

Hi, 

 

This might be my lack of experience, but I'm getting the following error in Alteryx when trying to update records via the output tool (connecting to SQL server) - 

 

"Primary Key required for Update option"

 

The issue is that I have a primary key on the table I'm updating, and I'm sending the data in the same exact format (as the current table structure) back to the table to update (and in fact I initially created the table using Alteryx, and then added the primary key via a sql update statement on the server). 

 

I'm attaching two screenshots - one of the Alteryx error and the other of the table showing the primary key. Note that I dropped and recreated the primary key several times, and re-ran the workflow several times. Also note that I'm testing with only 2 records. It seems very strange that I get this error when there is a primary key on the table. Can anyone assist with this? 

Alteryx Partner

It doesn't directly address your issue, but I have found it is more useful to use set based operations for updates/inserts particularly since Alteryx won't insert into a table with an identity column and running a couple hundred thousand update statements is way less efficient than a single update using a join. I get that you only have 2 records, but I tend to follow the same model regardless: write to a temp/staging table, execute an update or merge (upsert) set based operation in post-sql. Doing so would bypass your current issue or reveal why it isn't working (type mismatch, name slightly off, etc.).

Asteroid

Thanks, I was actually going to go that route, but since the max # of records this workflow would process on any given run is 70 (so 70 updates), it seems like the direct update would be best. I definitely understand what you are saying though. Is this perhaps a bug in Alteryx where it isn't able to access the PK and therefore says it is missing? 

Asteroid

Okay so after some internal dialogue in our group, it turns out that the connection type matters - OleDB will cause the primary key error, however using an ODBC connection will work. To me this looks like a bug in Alteryx as it appears Alteryx is throwing an incorrect error code. Anyways thanks for the help - maybe this can be put on the fix list...at least to throw the correct error. 

Quasar
Quasar

hey @ccagle We had this same problem, and there is a workaround. If you put 'Use tablename' in as a Pre-SQL statement it will work. It's silly and needs to be fixed. If you haven't yet, please put in a support ticket. The more outcry, the higher these things get prioritized.

Atom

For us the Pre-SQL needed to be 'Use [database]' - thank you for the pointer :-)

Labels