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?

10 REPLIES 10
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

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

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

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!

Helpful resources

Announcements
firstImage

PowerApps Monthly Community Call!

Join us next Wednesday for our Demo Extravaganza, October 16, 2019 8am PDT.

firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

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

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

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

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 345 members 4,905 guests
Please welcome our newest community members: