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

How to create SQL Virtual tables

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 ?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
dpoggemann
Super User
Super User

Hi @RobDp3 ,

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.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

dpoggemann
Super User
Super User

Hi @RobDp3 ,

 

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

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

View solution in original post

10 REPLIES 10
dpoggemann
Super User
Super User

Hi @RobDp3 ,

 

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

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

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.

 

dpoggemann
Super User
Super User

Hi @RobDp3 ,

 

  1. You do not need the GUID primary key anymore as stated in the article
  2. 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.
Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew

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.

 

dpoggemann
Super User
Super User

Hi @RobDp3 ,

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.

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
RobDp3
Frequent Visitor

Drew,

Thanks for your time and sharing your experience. 👍

RobDp3
Frequent Visitor

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 ?  

dpoggemann
Super User
Super User

Hi @RobDp3 ,

 

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

Hope this helps. Please accept if answers your question or Like if helps in any way.
Thanks,
Drew
EAguilera
Frequent Visitor

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

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (2,779)