Unanswered

The key didn't match any rows in the table

(0) Share

Report

Posted on by Melakh

I have a flow that consists of a button that triggers a strored procedure through an On-Prem data gateway to SQL Server.

When building the flow, the connection finds the list of stored procedures correctly so I can choose from the drop down list.

When running however, I get an error message:

{
  "status": 400,
  "message": "The key didn't match any rows in the table.\r\n     inner exception: The key didn't match any rows in the table.\r\nclientRequestId: 5145f632-8916-478b-9468-e1280192bf5f",
  "source": "sqlconnectionprovider-westeurope.am2-ase-001.p.azurewebsites.net"
}

This broadly seems to be the same error message as if I type the SPROC name in incorrectly, but I can't figure out why it's happening. Any ideas?

Categories:

General topics

I have the same question (0)

All responses (11)

Answers (0)

v-yuazh-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @Melakh

Could you please share a screenshot of the configuration of your flow?

The error message told that the key didn't match any rows in the table,I afraid that there is something wrong in your flow's configuration.

Please check your flow to see if you have some configuration wrong in the flow.

Best regards,

Alice

Was this reply helpful? Yes No
GabrielStJohn Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hey, @Melakh!

It appears as though would like some more information in order to further assist you properly with your issue. Please share any additional information that was requested in order to decrease the amount of time that it will take for you to be assisted!

Thank you for being an active member of the Flow Community!

-Gabriel

Flow Community Manager

Was this reply helpful? Yes No
duncfair 54 on at

Like (1)

Report
Copy link

Link copied!

Same issue:

The key didn't match any rows in the table. inner exception: The key didn't match any rows in the table. clientRequestId: A67FCF0B-8D02-4492-B022-C6C784CFCE34

Both the Sharepoint list and the table in SQL Server have a column called PivotID. It is an identity column set as Primary Key in the SQL table and is unique within the sharepoint list. I have added the [Current] column as added Dynamic content as the value to be updated in the target SQL Table.

Was this reply helpful? Yes No
duncfair 54 on at

Like (1)

Report
Copy link

Link copied!

I created a copy of this flow using Windows Authentication rather than basic, and it seems like that resolved the issue reported above. Now it looks like it SHOULD work but I get a failure with the following message.

{
  "status": 400,
  "message": "We couldn't convert to Number.\r\n     inner exception: We couldn't convert to Number.\r\nclientRequestId: c88507a3-e827-45f9-98e2-e6c3e9e09d68",
  "source": "sqlconnectionprovider-westus.tip0-westus.p.azurewebsites.net"
}

Was this reply helpful? Yes No
duncfair 54 on at

Like (1)

Report
Copy link

Link copied!

Now I get no error but the whole thing just hangs. The "To see it work now, modify a list item in the Sharepoint folder you selected. This may take a few moments." has been there for well over 10 minutes.

No failure, no success.

Was this reply helpful? Yes No
duncfair 54 on at

Like (1)

Report
Copy link

Link copied!

Clicked edit, made no changes, then tested again. Same error.

Body
{
  "status": 400,
  "message": "We couldn't convert to Number.\r\n     inner exception: We couldn't convert to Number.\r\nclientRequestId: 106464c3-b0b8-4f9d-b6b6-ed71227c6ed9",
  "source": "sqlconnectionprovider-westus.tip0-westus.p.azurewebsites.net"
}

Was this reply helpful? Yes No
agilmore 4 on at

Like (0)

Report
Copy link

Link copied!

Re the error: "The key didn't match any rows in the table"

When you run a Flow, the "Run Flow" dialog has an "edit connections". I found that the Flow was being run with the wrong SQL Server connection (I had more than 1 defined). I change to the correct connection and it ran fine.

Effectively this error message means "Flow can't find your table in the database".

Was this reply helpful? Yes No
MatthewBrooksST 24 on at

Like (0)

Report
Copy link

Link copied!

I was getting this error and the cause was that the SQL account I was using for my connection didn't have read/write access to the database. Once I gave the account the proper access, the connection worked successfully.

Was this reply helpful? Yes No
bkeano 136 on at

Like (1)

Report
Copy link

Link copied!

Just as a note here

Power Automate will show you a list of procedures in a DB, but that does not mean you have the rights, in your connection, to perform all the actions in the procedure. For example, your connection may have DB read and write only, but the procedure maybe creating and dropping tables, so the user calling the procedure does not have the rights.

It looks like Power Automate cannot determine this until the stored procedure is ran, then it interprets the failed response from the SQL server as 'key didn't match...'. Perhaps the error mesage could be improved here.

If you do not want to give your Power Automate connection\user more rights on a DB level, can simply change the procedure itself in SQL DB to be run by a diferent user than the one calling it.

Example
You have a connection on Power Automate with a SQL Auth type user with just read and write rights on the SQL DB level
The procedure you are running creates and drops tables.
Your Power Automate will not have rights to do this and you'll get the error ' the key didn't match....'
Instead of using or altering your connection with more rights, you can alter your proedure by adding the below line 'WITH EXECUTE AS 'DOMAIN\User' after the 'create\alter Procedure as' line

Now you can use the same connection in Power Automate and the procedure is run on server level by a user with elevated rights

Also see https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql?view=sql-server-ver15

Hope this helps someone.

Was this reply helpful? Yes No
MingLeiNL 2 on at

Like (0)

Report
Copy link

Link copied!

I encountered the same error. The root cause is that the gateway user of PowerAutomate has NOT been granted with a permission to execute the stored procedure, although the error message is misleading.

Was this reply helpful? Yes No