cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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?

10 REPLIES 10
Highlighted
Community Support
Community Support

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

Community Support Team _ Michael Shao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Microsoft
Microsoft

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
Highlighted
Advocate I
Advocate I

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.

Highlighted
Microsoft
Microsoft

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.

Highlighted
Anonymous
Not applicable

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

Highlighted
Microsoft
Microsoft

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

Highlighted
New Member

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.

Highlighted
Microsoft
Microsoft

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

 

 

Highlighted
New Member

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!

Helpful resources

Announcements
Check this Out

Helpful information

Featuring samples like Return to the Workplace and Emergency Response Applications

August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (8,312)