Using SQL server update rows action with uniqueidentifier as a primary key

Veli-Jussi Raitila 381

I have a table in Azure SQL database with a field of uniqueidentifier type as a (non-clustered) primary key.

Trying to update the table with update rows action - using the 'primaryKey' property - gives me the following error:

{ "code": "ServiceProviderActionFailed", "message": "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.'." }

The primary key consists of a single field and I have provided a value for it. Why does it not work?

EDIT: Clarification that the issue occurs in Logic Apps Standard using the InApp update rows action for SQL Server.

MayankBargali-MSFT 69,846 Reputation points

2024-01-25T11:13:55.8633333+00:00

@Veli-Jussi Raitila Thanks for reaching out.

The error message you received indicates that the input for the primary key is invalid. Please make sure that the value you provided for the primary key is in the correct format.

Also, please make sure that the value you provided is unique and does not already exist in the table. If the value already exists, you will not be able to update the row with that primary key value.

I will suggest to valid the same at the SQL end whether it works or not.
Veli-Jussi Raitila 381 Reputation points

2024-01-25T12:38:26.5033333+00:00

@MayankBargali-MSFT (or ChatGPT)

How do I ensure that the key is in the correct format? As I said, in the database the type is uniqueidentifier.

Looking at the workflow run history the value is passed as a string (the documentation does not specify any special treatment for UUIDs): primaryKey: "32bb6b87-ffe8-4aa2-b8e2-863f4cc41649"

As I also said, the field is defined as a PRIMARY KEY NONCLUSTERED. Doesn't this, by definition, make it unique?

The rest of the answer does not make sense. I'm using the update rows action. Which is means the row has to exist in order to update it. Not the other way around.
MayankBargali-MSFT 69,846 Reputation points

2024-01-25T13:58:57.9966667+00:00

@Veli-Jussi Raitila Thanks for sharing more details and what action you are using in your logic app. I assumed and missed that you are using the update action and that's where I mentioned that it should be unique value and doesn't exist already in the table.

The error message clearly states what is the issue and you are passing the primary key incorrect to the row ID column in your action.

As I mentioned, the primary key that you are passing is incorrect.

I have tested the same at my end and created UNIQUEIDENTIFIER PRIMARY KEY NONCLUSTERED table. Now I used the update row (V2) action and passed the Row id as the right Id which is present in the DB and try to update the database and it was successfully updated.

In case if your table schema is different or I misunderstood then please let me know.
Veli-Jussi Raitila 381 Reputation points

2024-01-26T05:29:19.94+00:00

@MayankBargali-MSFT Thank you for taking the time to attempt reproducing. However, I am using Logic Apps Standard and the InApp SQL Server (Update rows) action. The InApp actions are preferable to me using Standard. At least that is different in your experiment.

This is apparent from me referring to the primaryKey property whereas in your example it’s the ’Row id’. Furthermore, the error I get comes from ’ServiceProviderActionFailed’ which is another indicator.

Obviously one would expect them all work the same.
Stephane BOUILLON 0 Reputation points

2024-05-21T12:14:49.6666667+00:00
I'm having the same error. I don't see what is wrong with my code:

In the designer:

{ "type": "ServiceProvider", "inputs": { "parameters": { "tableName": "[dbo].[Orders]", "setColumns": { "OrderStatus": "Processing" }, "primaryKey": "@variables('ServerOrderReference')" }, "serviceProviderConfiguration": { "connectionName": "sql", "operationId": "updateRows", "serviceProviderId": "/serviceProviders/sql" } }, "runAfter": { "Send_Order": [ "SUCCEEDED" ] } }

And at runtime this results in:

{ "serviceProviderConfiguration": {

"connectionName": "sql",

"operationId": "updateRows",

"serviceProviderId": "/serviceProviders/sql"

},

"parameters": {

"tableName": "[dbo].[Orders]",

"setColumns": {

"OrderStatus": "Processing"

},

"primaryKey": "AZ_5540020202405210750432741"

}

}

In case the double quotes cause a problem, these are added at design time, how can I remove these ?

PS: this code worked fine, until I added an index for another column in the database, and if I remove the index, the code works again.

Stephane

Accepted answer

MayankBargali-MSFT 69,846 Reputation points

2024-01-26T11:01:05.2433333+00:00

@Veli-Jussi Raitila Thanks for more clarification on the action.

I was able to reproduce the issue and got the exact error that you have mentioned if I am passing the incorrect primary key for my table.

In the action, please make sure you are passing the primary key value correctly as below. Using the same table schema that I have shared previously I was able to update the FName field in my table. Make sure that you are passing the primary key without double quotes or mentioning the column name etc. as it is not needed. If you want to update multiple rows then you need to pass the primary key values separated by comma.

You can click on advance parameters and then select the Primary key or the where condition as per your need.

In case if you are still facing the issue then please share the screenshot of the primary key that exists on the table along with your action value from the trigger history.
Please sign in to rate this answer.
Veli-Jussi Raitila 381 Reputation points

2024-01-26T13:45:22.1066667+00:00

I think I got it now. The root cause seems to be an additional clustered index on the table. I cannot imagine why that would be a problem.

To reproduce it using your example, add a clustered index into the table (e.g. FName and LName fields).

Is there a bug where Logic Apps treats a clustered index as an alternative to the primary key - and even "overrides" the latter when defined on a table? Nothing obviously guarantees that a clustered index should also be unique and treated similarly to a primary key. Yes, sometimes, but not always.

If that indeed is the case, then at a minimum, the error message is misleading as there is nothing wrong with the primary key (and which threw both of us off).

MayankBargali-MSFT 69,846 Reputation points

2024-01-29T10:19:15.8166667+00:00

@Veli-Jussi Raitila It would be great if you could share the sample schema (CREATE SQL command with table schema) of your table so there is no further confusion.

Veli-Jussi Raitila 381 Reputation points

2024-01-30T07:14:28.99+00:00

This is a simplified example what I was able to reproduce it with:

CREATE TABLE [dbo].[UpdateRowsTest] ( [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED, [FirstName] varchar(50), [LastName] varchar(50) ) GO CREATE CLUSTERED INDEX IX_UpdateRowsTest ON [dbo].[UpdateRowsTest] ([FirstName] ASC, [LastName] ASC) GO

MayankBargali-MSFT 69,846 Reputation points

2024-01-31T03:36:34.8033333+00:00

@Veli-Jussi Raitila I will review and test it at my end and get back to you.

MayankBargali-MSFT 69,846 Reputation points

2024-02-06T11:41:33.92+00:00

@Veli-Jussi Raitila Apology looks like my previous comment was not posted.

With your schema I have added one more column and you need to use the where condition as you have the UNIQUEIDENTIFIER type when defining multiple columns.

CREATE TABLE [dbo].[UpdateRowsTest] ( [Id] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED, [FirstName] varchar(50), [LastName] varchar(50), [Custom] varchar(50) ) GO CREATE CLUSTERED INDEX IX_UpdateRowsTest ON [dbo].[UpdateRowsTest] ([FirstName] ASC, [LastName] ASC) GO INSERT INTO [dbo].[UpdateRowsTest] VALUES ('bcbeea9a-43ba-4cc2-89ec-cde1b7324ab2', 'Mayank', 'Bargali','Test')

When you try to define the primary key it will not able to case it to unique identifier when using the Primary key parameter. But in case if the schema would be as below and it would varchar then you could have used the primary key parameter as below.

CREATE TABLE [dbo].[UpdateRowsTest] ( [Id] VARCHAR(50) NOT NULL PRIMARY KEY NONCLUSTERED, [FirstName] varchar(50), [LastName] varchar(50), [Custom] varchar(50) ) GO CREATE CLUSTERED INDEX IX_UpdateRowsTest ON [dbo].[UpdateRowsTest] ([FirstName] ASC, [LastName] ASC) GO INSERT INTO [dbo].[UpdateRowsTest] VALUES ('bcbeea9a-43ba-4cc2-89ec-cde1b7324ab2', 'Mayank', 'Bargali','Test')

Let me know if you have any queries or concerns.

Veli-Jussi Raitila 381 Reputation points

2024-02-06T13:45:28.7366667+00:00

There is no guarantee that I have any other field values (e.g. those in the clustered index - and unrelated to the actual primary key) available in the flow to perform the update, and I still find it misleading that I cannot use a property named "Primary Key" to pick a record based on a field which is literally defined as the primary key for a table.

Your second example does not explicitly show that it is a casting problem either as you are also providing the values for fields for the clustered index (and not part of the primary key). Rather, it seems to me that the SQL Server action treats whatever fields are included in the clustered index at the primary key for a table - and that is, in my opinion, an incorrect assumption. Regardless, I have accepted the answer.

MayankBargali-MSFT 69,846 Reputation points

2024-02-08T04:34:05.5433333+00:00

@Veli-Jussi Raitila I will check with my team and pass your feedback and will update you once I head back from them.
Sign in to comment

Share via

Using SQL server update rows action with uniqueidentifier as a primary key

0 additional answers