cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MichelH
Level: Powered On

SQL get rows returns no rows on select * from on premises database

Again I get a peculiar behaviour from SQL Get rows on an on premises database.

 

This time I'm trying to simply get rows from a table with about 500 records.

No selection is made so in theory this is a 'select * from'.

 

When building this, I connect to the database, I see the tables in the database, (I select the table name from the dropdown, so I have rights to see), I can see the field-names in the dynamic view, so also these are returned via the gateway.

 

 

Getrows_config.jpg 

 

But then when I run the flow, the first loop get the tables names, but then I get this fault: "The key didn't match any rows in the table."

Key didn't match.jpg

What key?

 

How can find out what goes wrong here?

Can it be possible I can see table names and field names, but cannot be allowed to see the contents of the records?

 

Someone said I should try to get the tables before trying to access the records.

Does that really serve any purpose?

 

Thanks for any enlightenment  :-)

 

Michel

1 ACCEPTED SOLUTION

Accepted Solutions
Coder51
Level: Powered On

Re: SQL get rows returns no rows on select * from on premises database

 

@MichelH the issue is not that the connection is switching throughout the flow, but rather that the connection chosen for SQL Server actions when designing the flow isn't the one used when running the flow.  At least, not initially.  Any changes made regarding which connection is used by a flow via the Run Flow pop-up dialog seem to stick.  I haven't had any issues with subsequent runs.

 

Also, getting an empty result set when you know a record exists is symptomatic of this problem.

10 REPLIES 10
Community Support Team
Community Support Team

Re: SQL get rows returns no rows on select * from on premises database

Hi @MichelH,

 

The error message means that you have a query in which a row is selected based on a key value but that value doesn’t exist. Do you have any key columns in the table?

From the screenshot, it seems that there is no special configuration in the action Get rows. Have you changed the table name in SQL Server?

Please make sure the table named exactly in SQL Server.

I have made a simple test to get rows from SQL Server table, and it just works fine.

1.PNG

Please feel free reply if you are still having the issue.

 

Best regards,

Mabel

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Administrator
Administrator

Re: SQL get rows returns no rows on select * from on premises database

Hello, @MichelH!

Thank you for posting on the Flow Community Forum! Have you had an opportunity to apply @v-yamao-msft‘s recommendation to adapt your Flow? If yes, and you find that solution to be satisfactory, please go ahead and click “Accept as Solution” so that this thread will be marked for other users to easily identify!

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

-Gabriel
Flow Community Manager

-Gabriel
Microsoft Flow Community Manager
MichelH
Level: Powered On

Re: SQL get rows returns no rows on select * from on premises database

Hi Mabel, Gabriel,

 

The query done by flow AFAIK  is a 'select * from' , so there is no key, so it cannot be wrong.

 

The table MH_SAPHeaders2 is a view on an on premises SQL server, in with no keys/indexes are defined.

 

 SAPHead.jpg

I'm basically just trying to find out how and where I can see why this fails in my situation.

Surely I'm doing something wrong.

The error message is not helping me. 

Administrator
Administrator

Re: SQL get rows returns no rows on select * from on premises database

Hello, @MichelH!

Thank you for posting a reply to a topic on the Microsoft Flow Community! It appears that the users who you have been engaging in this thread with are now awaiting your reply! Please follow-up on this thread ASAP.

Thank You!

-Gabriel

Microsoft Flow Community Manager

-Gabriel
Microsoft Flow Community Manager
Coder51
Level: Powered On

Re: SQL get rows returns no rows on select * from on premises database

I am also experiencing this issue for On-Premesis Databases, though in my experience it goes much further than what the OP describes.  I have been unable to retrieve data using the SQL Server Connector period.  I can connect to the server, select a table, even retrieve the data in the designer portion of the "Transform data using Power Query" action.  But attempt to actually run a flow that pulls that data?  Even in test mode?

 

Fails every time.

MichelH
Level: Powered On

Re: SQL get rows returns no rows on select * from on premises database

I see what you are saying.

I have one DB on this server where it all works. 

Credentials and settings for other DB's are similar.

My guess is that someone can tell me how to figure out what goes wrong.

It is likely the gateway config, but I have yet to understand how to interpret the lines of the logs.

Coder51
Level: Powered On

Re: SQL get rows returns no rows on select * from on premises database

I just got it working!  It appears there is a bug in how Microsoft Flow selects the On-Premises database connection to use when running a flow. Namely, it ignores the connection chosen for an Action during flow design and instead defaults to the first connection in the list.  So when multiple SQL Server connections exist (say, a production and testing instance), Flow may default to the wrong one while running.

Screenshot - 11_1_2018 , 10_02_03 AM.png

 

In my case, the objects I was querying only existed on testing, so the query failed because flow tried to query production for the data.  After manually selecting the correct datasource during testing, the SQL Server Connector started working for me in both test and regular run modes.

 

MichelH
Level: Powered On

Re: SQL get rows returns no rows on select * from on premises database

I have some issues there to, since I created multiple "connections" with the same name.

For some reason Flow says there was an issue connecting, so I retyped credentials but it creates multiple "connections". I need to add a number to the "connection" name to distinguish. 

Also I notice that it always deletes the credentials (username and password) when you try to edit them.

 

I would assume however that the flow has to stay with the credentials used to make the flow.

Also in my case I do a 'Get tables' that still works.

I can't imaging that flow would switch to another "connection" throughout the flow, although indeed each part has it own connector.

But then how can I see table names and field names?

This has to mean the credentials are fine.

But I still get this behaviour or I get an 200 OK but with an empty record [] when I know for sure a matching record exists.

Coder51
Level: Powered On

Re: SQL get rows returns no rows on select * from on premises database

 

@MichelH the issue is not that the connection is switching throughout the flow, but rather that the connection chosen for SQL Server actions when designing the flow isn't the one used when running the flow.  At least, not initially.  Any changes made regarding which connection is used by a flow via the Run Flow pop-up dialog seem to stick.  I haven't had any issues with subsequent runs.

 

Also, getting an empty result set when you know a record exists is symptomatic of this problem.

MichelH
Level: Powered On

Re: SQL get rows returns no rows on select * from on premises database

Indeed, when I share the flow it becomes clear what connections are actaully used.

Somethimes the connections ave disappeared in the flow and the person with whom the flow is shared needs to re-add them.

 

I really dislike this not being clear upfront about what connection is used in the flows, but this is how it seems to work.

Helpful resources

Announcements
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 Release Wave 2 Plan

Power Platform 2019 Release Wave 2 Plan

Features releasing from October 2019 through March 2020

thirdimage

Flow Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

thirdImage

New Flow Community Board!

Check out the new Microsoft Flow Community Blog Topic Suggestion board!

fourthImage

Australia Summit

Travel to Melbourne and network with thousands of peers!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Flow Community Video Gallery!

Users Online
Currently online: 71 members 4,442 guests
Please welcome our newest community members: