Unanswered

How to create SQL Virtual tables

(0) Share

Report

Posted on by RobertLHU

I'm looking to create virtual tables in Dataverse environment connected to SQL Server. I'd appreciate understanding how to setup the SQL tables. If I have existing data, how do I create new GUID key. What's required for the second "primary field" in addition to the PK ?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (10)

Answers (2)

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @RobertLHU ,

I just did a presentation late last week on this exact topic at a User Group utilizing the Virtual Connectors in preview which makes it very easy to create virtual tables connected to SQL Server.

Please see the following article which walks through step by step:

https://docs.microsoft.com/en-us/power-apps/developer/data-platform/virtual-entities/create-virtual-tables-using-connectors?tabs=sql

Was this reply helpful? Yes No
RobertLHU 50 on at

Like (0)

Report

thank you for your response. I've been reading through that article, but there seems to be some missing pieces.
* does my SQL table require a GUID PK?
I've successfully created a virtual table from that article, but I get an error when I try to edit in Excel like other dataverse tables.
* A row created in data set cr_dbo_tvcalendarvs was not published. Error message: 'Write operation for Virtual Entity is not allowed in Batch request. '

Is Excel a dead-end for editing a virtual table, or am I perhaps configuring the data wrong?
I created two virtual tables, one with GUID and one without GUID, and I get the same error.
any thoughts you have are appreciated.

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report
Hi @RobertLHU ,

You do not need the GUID primary key anymore as stated in the article

Please see limitations of the SQL COnnector as this is what is used behind the scenes (https://docs.microsoft.com/en-us/connectors/sql/) and if you look at the actions it only supports a single row update (https://docs.microsoft.com/en-us/connectors/sql/#actions) vs. multiple.

Was this reply helpful? Yes No
RobertLHU 50 on at

Like (0)

Report

Thank you for confirming those details. One last question please before finishing this thread 😊 🤔 Editing SQL in Excel was a valuable potential use case that I now realize is out of scope... What incremental value do you see in creating a virtual table for SQL Server instead of just using the SQL connector straight away ? ? I don't currently see the justification for the extra work.

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @RobertLHU ,

One of the key use cases is:

Provide ability to extend tables in a separate system that you are not able to edit but provide critical information. Example is one system I would looking for with a customer that needed to provide "Good Faith Estimates" in Healthcare but their EMR system did not have this functionality. We wanted to connect the details from the Dataverse tables to patients in the EMR without copying them into the Dataverse through Data Flows, Azure Data Factory, other middleware etc.

You can utilize this to create those relationships in your Dataverse application to the new tables and even create simple "CRUD" methods to maintain the SQL data as well.

Was this reply helpful? Yes No
RobertLHU 50 on at

Like (0)

Report

Drew,
Thanks for your time and sharing your experience. 👍

Was this reply helpful? Yes No
RobertLHU 50 on at

Like (0)

Report

Hello again @dpoggemann ,
One more question on potential use cases for these virtual tables. Can you use a virtual table as a target for a dataflow ?

Was this reply helpful? Yes No
Verified answer

Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @RobertLHU ,

Yes you can. Just did it and it worked great!

Was this reply helpful? Yes No
Eliel Aguilera 51 on at

Like (0)

Report

Hi @dpoggemann, I'm sorry to re-use this post, but I'm actually locked into a situation with the Virtual Connectors for Dataverse. I followed the tutorial you mentioned before and I got the tables and the data into Dataverse, but every time I want to do an operation over the table I got a "BadRequest : The specified item '< id >' is not found". To give a little of context, I tried in two different enviroments, servers, databases and several tables with no luck. Maybe I miss something? Thanks in advance for help!.

Was this reply helpful? Yes No
Drew Poggemann 9,287 Most Valuable Professional on at

Like (0)

Report

Hi @EAguilera ,

Please open another question with your issues and explain the operations that are causing issues. This will help you get better support from the community because it will be a question that is not already answered. I am happy to try and help you through this. Please try to be specific in the details on the challenges.

Was this reply helpful? Yes No