UpdateRows in logic app fails with error BadRequest

Stephane BOUILLON 0 Reputation points
2024-05-21T13:53:39.89+00:00

In my logic app I'm trying to update a row in a sql database using the SQL internal connector, executing an UpdateRows activity. I pass in the column I want to update, as well as the primary key. I don't see what could be wrong with my code, but I get a BadRequest error:

The service provider action failed with error code 'BadRequest' and error message 'Invalid input for primary key. Ensure that a value for each field in the primary key is provided.'.

I have this In the designer:

User's image

and the resulting code is:

{
    "serviceProviderConfiguration": {
        "connectionName": "sql",
        "operationId": "updateRows",
        "serviceProviderId": "/serviceProviders/sql"
    },
    "parameters": {
        "tableName": "[dbo].[Orders]",
        "setColumns": {
            "OrderStatus": "Processing"
        },
        "primaryKey": "AZ_5540020202405210750432741"
    }
}

I get the BadRequest error at runtime with the raw input below:

This logic app has always worked without issue until I added an index on the table for another column. When I remove the index from the table, the code works again.

Can anybody confirm if this is a bug or by design, and what I might be doing wrong ?

Kindest regards,

Stephane

Azure SQL Database
Azure Logic Apps
Azure Logic Apps
An Azure service that automates the access and use of data across clouds without writing code.
2,927 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Alberto Morillo 33,176 Reputation points MVP
    2024-05-21T14:26:11.9433333+00:00

    Please make sure you are passing the primary key value. You cannot pass the column name as parameter.

    In case, you would like to update more than one row at a time, you need to pass the primary key values separated by comma.


  2. Stephane BOUILLON 0 Reputation points
    2024-05-22T12:02:44.4166667+00:00

    My conclusion is that the UpdateRows activity in the SQL Connector has some bug or flaw that makes this fail, when there are indexes on the target table. I don't know why this happens, and when I remove the index from the table the UpdateRows works as expected.

    Since I needed the indexes on the table, I used a Stored Procedure as a workaround to do the update and this works as expected.

    Stephane