Unanswered

I can't add a SQL virtual table view

(5) Share

Report

Posted on by TonyCasilla

A few days ago I was able to add my SQL visualizations to dataverse as normal, but since yesterday I cannot add any visualization because:

- it asks me for a primary key

-sql visualizations do not have primary keys

I would like to know if this is a temporary limitation or It is only an isolated case or if it is no longer going to be possible to use visualizations in dataverse, what will be the alternative since I need to use the sql visualizations.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (11)

Answers (0)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

@NHelgren , Any thought on this one? I have not worked in this area of virtual tables...

Was this reply helpful? Yes No
rbandi 10 on at

Like (3)

Report

It used to work in the previous version. The latest update is causing this issue. I can't create virtual tables using any SQL views. It's a bug. Please fix it.
Before any Number column comes up as the primary key in the Select drop-down.

Was this reply helpful? Yes No
rbandi 10 on at

Like (0)

Report

TonyCasilla,
Temporarily, we fixed this by creating a table with a Primary key using the view and updating the table by running the Merge command job every 30 sec. Virtual table created using the table. I hope this will help.

Was this reply helpful? Yes No
Viliam Juricek 70 on at

Like (1)

Report

Hi @TonyCasilla ,
seems that @rbandi is correct, I have the same issue and there are several posts from the end of last week with same problems.
Unable to add on-premise SQL Server tables to Data... - Power Platform Community (microsoft.com)
Unable to choose Primary Key during Virtual Table refresh

Was this reply helpful? Yes No
matthhias 48 on at

Like (0)

Report
I have the same issue. I did add a SQL view as virtual table some day ago and it went fine.

Today the similar approach does not work and is giving me the error message "The table doesn't have a primary key"

But this view definitely includes a primary key field as well as the successfully included view some days ago did.
I stripped the new view down to a simple SELECT <all fields listed> FROM <Table> for testing purposes after I received the error message. Does not help.

The successful connection some days ago worked with a view that even includes a join between two tables. This vitual table still works, by the way.

This is how the primary key is defined in the table where the view refers to:

CREATE TABLE [dbo].[Assessment](

[AssessmentID] [int] IDENTITY(1,1) NOT NULL,

...

...

) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Assessment] ADD CONSTRAINT [PK_Assessment] PRIMARY KEY CLUSTERED

(

[AssessmentID] ASC

)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]

GO

Was this reply helpful? Yes No
acalder 5 on at

Like (0)

Report

Any word on this? I am trying to create a virtual table from a SQL view. I added GUID into the view using NEWID().

Was this reply helpful? Yes No
Fiasco 42 on at

Like (0)

Report

Issue persists. I cannot add a virtual table of a view due to missing primary key. To the list of things that don't work: Adding an existing ID column to the view; and casting that ID column as INT.

Was this reply helpful? Yes No
frederikbisback 222 on at

Like (0)

Report

Any news on this topic?
@NHelgren,

Was this reply helpful? Yes No
Simon Phillips 1 7 on at

Like (1)

Report
I was able to create a virtual entity on a view (I didn't get the error above) - but now I'm getting an error when I retrieve individual rows for the virtual entity. It is really frustrating when documented features do not work.
Dataverse queries to return multiple rows work correctly.
Dataverse queries to return just one row give a primary key error.
Query:
https://exampleenviromment.crm11.dynamics.com/api/data/v9.2/example_entity(2025b5af-2069-ee11-b006-6045bd121e9a)?$select=example_fieldname
Response:
{"error":{"code":"0x80040224","message":"APIM request was not successful : StatusCode : BadRequest, Reason : Primary key is compound by 0 elements but 1 were provided., Response from APIM: {\r\n \"status\": 400,\r\n \"message\": \"Primary key is compound by 0 elements but 1 were provided.\\r\\nclientRequestId: 26bd0a22-d200-4f19-bac8-7f4b7181e54e-self\",\r\n \"error\": {\r\n \"message\": \"Primary key is compound by 0 elements but 1 were provided.\"\r\n },\r\n \"source\": \"sql-ukw.azconn-ukw-001.p.azurewebsites.net\"\r\n}"}}

It appears this is a runtime problem. My underlying view was named vw_example_entity, and querying the table example_entity. If I just rename the table to the name of the view, the call works!

It looks like a clumsy workaroud for now is to ditch views and have my tables prefixed with vw_!
Does anyone know of a way to change the underlying table name in the virtual entity metadata to avoid this?

I've also raised an issue against the documentation, since it suggests this works, and it hasn't for 6 months.

Was this reply helpful? Yes No
Tim Dutcher 30 on at

Like (1)

Report

I agree with everyone that the Microsoft Dataverse development team needs to fix this ASAP. We have a view in Azure SQL with tens of millions of records and we need to provide read-only access for Dataverse users to bring up records in a grid as well as a form. The latter currently leads to the error "Primary key is compound by 0 elements but 1 were provided" and the system returns the user back to the grid view.

One work-around I haven't tried yet with virtual connector-based virtual tables (but have with custom-built virtual tables) is to alter the FetchXml for the Retrieve message using a plug-in registered as pre-operation. For some of our other virtual tables, we're able to use a plug-in to alter the FetchXml or QueryExpression before Dataverse translates it to OData and makes the external request. So, to work around the bug described in this thread, the plug-in will need to alter the query such that the OData call will look like this:

https://exampleenviromment.crm11.dynamics.com/api/data/v9.2/example_entity?$filter=myprimarykey eq '2025b5af-2069-ee11-b006-6045bd121e9a'&$select=example_fieldname

Again, I haven't implemented the pre-operation plug-in for this specific issue, but I have for other use cases, such as implementing 1:N functionality without the need to store the parent record's GUID in SQL. The plug-in alters the FetchXml query to retrieve the SQL-based data using other columns rather than the default uniqueidentifier column for the relationship.

Was this reply helpful? Yes No