SQL Connection Problem: "does not define any key columns"

[MVabalaitis]

I am using the web application for development and have had no issues connection and viewing data through my SQL gateway. All of a sudden tables which were previously connecting successfully are returning the following error message: "does not define any key columns". Has anyone seen this before and have any suggestions / work arounds?

[v-micsh-msft]

Hi @MVabalaitis,

Could you please share about your Session ID?

Under Account->Diagnostics->Session Details

Currently there are some issues when adding new connections for SQL Server.

In addition, for suggestions, please tkae a try to connect to a table with Text column and a Primary Key defined, see if PowerApps could load it successfully.

Regards

[Cameron]

Hi @MVabalaitis, We have resolve the issue that was causing problems creating new connections to SQL Server, please try it again. Also, let us know if you were able to connect to a simple table with a primary key. Thanks, -Cameron

[CaCi]

I am getting this same error when connecting to a SQL database through an on-premises gateway. Any suggestions? I have successfully connected to this data in PowerBI, but haven't had any luck yet in PowerApps.

[Cameron]

If this table has no primary key, and you are performing any operations against this table which require a primary key (such as GetItem, UpdateItem, DeleteItem) then this error message is expected. If you want to do these kinds of operations against this table, you should consider adding a primary key.

[Anonymous]

Hi Cameron,

I am still getting this "error does not define any key columns" could you please provide your input

[Cameron]

Yes indeed. There are some operations which require a primary key in order to operate properly - these actions include "GetItem", "UpdateItem" and "DeleteItem" - since PowerApps allows all of these operations, it requires that your table contain a primarykey. By adding a primary key to your table, you will be able to use this table without issue.

Thanks,

-Cameron

[kennethkelly07]

@Cameron is there any way to designate a key column when adding the table? I have tables that contain a PK but these are generated from another table.

I could use SET IDENTITY_INSERT to ON and make the PK columns actual keys for my tables, but they are integrated heavily in other processes and would rather not alter them at risk of breaking(MANY) downstream dependencies.

[Cameron]

Kenneth,

For this purpose, you do not need to set the column as an identity, but as a PrimaryKey.  I understand your reticence to alter the table schema if it is used for other purposes, though I do not think that identifying a PrimaryKey would be problematic to any process that does not need it. If there is someone else who manages SQL schemas and services for you, be sure to touch base with them.

The PrimaryKey must be identified by SQL as such.

Let me know if I can help any further.

-Cameron

[kennethkelly07]

Thanks for the quick response Cameron.

If it is an issue, I can always replicate the table with the altered schema. Thanks.

It would be an awesome feature to be able to designate a key column on tables where none exists, if added. For example, in Redgate SQL Data Compare when comparing two tables, it requires a key but allows this designation from the user.

Thanks again!