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

[MichelH]

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.

 

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

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

[v-yamao-msft]

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.

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

Best regards,

Mabel

[GabrielStJohn]

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

[MichelH]

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.

 

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. 

[GabrielStJohn]

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

[Anonymous]

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]

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.

[Anonymous]

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.

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]

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.

[Anonymous · Accepted solution]

@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]

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.