You’re offline. This is a read only version of the page.
Announcements
45
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.
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.
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:
Share
Report
All responses (
Answers (
https://exampleenviromment.crm11.dynamics.com/api/data/v9.2/example_entity(2025b5af-2069-ee11-b006-6045bd121e9a)?$select=example_fieldnameResponse:
{"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.
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.
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
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)
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.
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
