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

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

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?

Community Support Team
Community Support Team

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

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

Microsoft Employee

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

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
Level: Powered On

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

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.

Microsoft Employee

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

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.

Aniket
Level: Powered On

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

Hi Cameron,

 

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

Microsoft Employee

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

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
Level: Power Up

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

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

Microsoft Employee

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

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
Level: Power Up

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

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!