mikefactorial / sqlvirtualentitydataprovider Goto Github PK

Table with a lookup column based on a Virtual Entity does not work in TDS Endpoint

While querying a Dataverse table with a lookup column based on a Virtual Entity using SQL Server Management Studio an error msg was issued. The error msg was “Msg 40000, Level 16, State 1, Line 1 Invalid column name …” and the column was the primary name column of the virtual entity. The virtual entity is being provided by the Sql Virtual Entity Data Provider. The SQL Server Management Studio uses the TDS Endpoint to connect to the Dataverse.

The following print screen illustrates this issue.

Getting Invalid Argument error on form load when there is 1:N relationship with another virtual entity

It doesn't seem possible to map one external field to multiple CDS fields. A use case I had was the other PK is an int field and I wanted to show that on a form or view, so I had to map that as my PK in CDS but I can't display that field. When I added it to a second field, it just came back empty.

hi,

i want to use this sql connector to connect our on-premise database, instead of the DB on Azure sql. in the video on YouTube (https://www.youtube.com/watch?v=hmSudDrbGK4 ), Mike mentioned we can set up a public gateway for the on-premise database then use it in this sql connector.
i am wondering how we can set up the public gateway so we can connect the on-premise database from CRM online? is this some reference we can use?
https://tahoeninjas.blog/2020/03/05/accessing-your-on-prem-data-using-on-prem-data-gateway/
https://crmkeeper.com/2019/06/07/on-premise-data-gateway-setup/

thank you!

Awesome solution. Could you add support for multiple DBs/connection strings?

Running standard queries (list records matching search criteria) is working well, however when I try to run any fetchXml that contains an aggregate (count, max, etc) I get the error "FetchExpression cannot be converted because aggregates aren't supported by QueryExpression"

The odd thing is that I think this is being thrown before reaching the SqlVirtualDataEntityProvider or even attemping fetchXml to Sql conversion.

Do you know if SqlVirtualEntityProvider can support aggregate fetchXml queries?

This is a known issue ATM. If you have a lookup to a SQL Virtual Entity you won't be able to add that column as a Quick Find Searchable Column

The trace log confims that query run succesfully against TDS for lookups but crm throws an "invalid argument error".
The filtering by lookup field works perfectly fine, displaying non-lookup fields also ok.

Not sure if this is a bug from the provider or Power Automate.

When I set up a "When a record is selected" on my virtual entity, and reference the properties lower in my flow, all field types seem to work except for int fields. When I use an integer field (assigning it from the selected entity to a int variable even), I get an error that the trigger is broken (from flow) and an error trying to invoke it in CDS. I've successfully used text, yes/no, dates and PK fields.

This is the error message I get when invoking it in CDS:
Error: The input body for trigger 'manual' of type 'Request' did not match its schema definition. Error details: 'Invalid type. Expected Integer but got String.,Invalid type. Expected Integer but got String.'.

The field is configured as Whole Number in CDS, and int in the database.

The External Name at the field level is case sensitive when looking at a field on a form, but not looking at the view.