CODEWITHSUNDEEP
sqlsql-serverazureazure-sql-databaseazure-logic-apps
scape
scape

Reputation: 707

Azure LogicApp cannot get rowid from SQL Server

I have an Azure logicapp that connects to a SQL Server database in Azure. The table has a uniqueidentifier column as the primary key. In the logic app the delete or update requires the rowid in order to perform update, the primary key does not work here and throws an error when running, stating that the table does not have a primary key. It does, and is additionally confirmed when inserting a row twice in the logicapp it knows about the primary key's constraint, being unique.

If I Get Rows and filter by the primary key column, the resulting array lists each item found (the one obviously) but does not make available the rowid as a dynamic expression.

How is one to actually get the rowid? Is it possible to update a row using just the primary key?

UPDATE:

The uuid is generated outside of the logicapp and is passed to it as a string. It should be noted that inserting works just fine.

Here the logicapp requires a rowid specifier, but using the uuid fails. I thought I could then instead get all rows filtered by uuid and update them, as a work around, but I could not get the rowid. Another person had a similar issue (https://stackoverflow.com/a/43516709/1298523) and could not get the rowid without editing the source code; however this method of adding the rowid in the body section of the code no longer seems to work as the result is empty. enter image description here The logicapp error I receive during an update or delete when using the uuid is:

{ "status": 400, "message": "Execution Failure\r\n inner exception: The specified table has no primary key. Update and delete operations are not supported.\r\nclientRequestId: 555", "source": "sql-555.p.azurewebsites.net" }

With that said the insert goes fine and the uuid I pass get's placed into the table.

Insert result: enter image description here

Upvotes: 0

Views: 2979

Answers (4)

Alok Maheshwari
Alok Maheshwari

Reputation: 1

I'd similar issue, but setting column (for which value is passed in Rowid) as primary key in the database table fixed the update row execution error. Columns to be updated can be added as parameters with the respective values.. Thank you !

Upvotes: 0

Nick Weimer
Nick Weimer

Reputation: 637

Instead of rebuilding the logic app from scratch, you could instead press "Refresh" (right next to the "Edit" button when going to edit your logic app). Worked for me

Upvotes: 1

scape
scape

Reputation: 707

I figured it out, I actually deleted the entire logicapp and rebuilt it and now it recognizes that the uuid column is a PK. I believe when the app is first connected to SQL it stores the schema and never updated afterwards. I had rebuilt the connection a few times but the only thing that got it to update was by rebuilding the logicapp itself. I'm a bit convinced this is a bug, so I'm leaving here as an answer in case anyone ever comes in to this issue in the future

Upvotes: 3

Greg Low
Greg Low

Reputation: 1586

If you have a column default (like NEWID()) on the primary key, remove it.

Generate the uniqueidentifier directly in your logic app, and just insert it like any other column into the table in Azure SQL DB. If you do that, you already have the GUID value that you can then use later in the same app code.

If that's not the issue, I think you need to give us some code to read so we can help more.

Upvotes: 0

Related Questions