UpdateRows in logic app fails with error BadRequest

Question

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:

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

Answer 1

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.

Answer 2

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