Skip to main content

Notifications

Announcements

Welcome to the Power Platform Communities
News and Announcements icon
Community site session details

Community site session details

Session Id : vK2NN34lqIz3elypmKMg6Y
Power Platform Community / Forums / Power Automate - General Discussion / The key didn't match a...
Power Automate - General Discussion
Unanswered

The key didn't match any rows in the table

Like (0) ShareShare
ReportReport
Posted on 21 Jun 2018 17:46:40 by Melakh 12

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:
Connecting to Data
  • fly_cast Profile Picture
    fly_cast 2 on 08 Apr 2022 at 22:38:13
    Re: The key didn't match any rows in the table

    I had the same error. It was caused by a TRUNCATE TABLE command. The user had permission to execute the stored proc, but not permission to execute a TRUNCATE.

  • MingLeiNL Profile Picture
    MingLeiNL 2 on 23 Mar 2022 at 10:29:40
    Re: The key didn't match any rows in the table

    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.

  • bkeano Profile Picture
    bkeano 136 on 18 Nov 2021 at 10:38:44
    Re: The key didn't match any rows in the table

    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.

     

  • MatthewBrooksST Profile Picture
    MatthewBrooksST 24 on 05 Jan 2021 at 13:43:35
    Re: The key didn't match any rows in the table

    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.

  • agilmore Profile Picture
    agilmore 4 on 18 Jun 2019 at 05:07:00
    Re: The key didn't match any rows in the table

    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". 

  • duncfair Profile Picture
    duncfair 37 on 22 Aug 2018 at 16:59:51
    Re: The key didn't match any rows in the table

    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"
    }

     

  • duncfair Profile Picture
    duncfair 37 on 22 Aug 2018 at 16:43:23
    Re: The key didn't match any rows in the table

    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.

  • duncfair Profile Picture
    duncfair 37 on 22 Aug 2018 at 16:01:36
    Re: The key didn't match any rows in the table

    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"
    }

  • duncfair Profile Picture
    duncfair 37 on 21 Aug 2018 at 21:31:10
    Re: The key didn't match any rows in the table

    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.

     

     

  • GabrielStJohn Profile Picture
    GabrielStJohn on 27 Jun 2018 at 21:58:18
    Re: The key didn't match any rows in the table

    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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities

Quick Links

Thomas Rice – Community Spotlight

We are honored to recognize Thomas Rice as our March 2025 Community…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 146,508 Most Valuable Professional

#2
RandyHayes Profile Picture

RandyHayes 76,287 Super User 2024 Season 1

#3
Pstork1 Profile Picture

Pstork1 65,369 Most Valuable Professional

Leaderboard

Featured topics

How to Ask for Help with Your Power Automate Workflow

Product updates

Microsoft Power Platform Community release plans