Microsoft Dynamics 365 | Integration, Dataverse...

Virtual table on SQL View

(0) Share

Report

Posted on by GianlucaN

Hello,

I should create a new virtual table, from a view on my sql server on prem.
I've created a new view on my Sql Server, with a column that contain a GUID.
So, I start to create a new virtual table, choose a correct sql server and relative view, but when the procedure start to read the columns, appear an error that indicate "No primary key on table" and i cannot create a virtual table.

There is something particolar that I must do for create a virtual table from a SQL View?

Thanks in advance

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Eiken Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi,

Please refer to the following video

Create a Virtual Table for SQL with the Virtual Connector Provider - YouTube

Was this reply helpful? Yes No
GianlucaN 6 on at

Like (0)

Report
Copy link

Link copied!

Hello,

Thanks for the reply.

I've not a problem to create a new virtual table from a SQL Table.

If I create a virtual table from a SQL Table everythings works fine.

My problem is create a virtual table from a SQL View.

When I try to create a Virtual table and select SQL view like source, the procedure not find a Primary Key even if there is a Guid for each record in the view.

Any suggestions?

Thanks

Was this reply helpful? Yes No
Suggested answer

Bipin D365 28,985 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi,

Creating any Virtual/Custom table requires two fields minimum

1. Unique identifier - Guid field

2. Name - Primary key

Make sure you add one column apart from PK field in SQL view

Was this reply helpful? Yes No
GianlucaN 6 on at

Like (0)

Report
Copy link

Link copied!

Hello,

I've already create a virtual table on Dataverse, but based on SQL table.

With SQL Table, I found correctly a key for association for my Virtual table.

But with SQL View the procedure not found a Key to associate, and the field remain uneditable.

In my Sql View there are 5 column and 1 GUID Column.

Thanks in advance

Was this reply helpful? Yes No
Community member 2 on at

Like (0)

Report
Copy link

Link copied!

Hello GianlucaN,

I'm also facing the same issue, can you please share the solution details if you have.

Thanks in advance!

Was this reply helpful? Yes No
Community member 4 on at

Like (0)

Report
Copy link

Link copied!

It looks like this is an on-going issue.
I have implemented this same scenario and it's still not working for views.

I even tried to trick the system by converting my view into a table, used the table to build the virtual table in Dynamics then went to my sql database and made the table into a view with the same field names and metadata.

Nothing works.

Was this reply helpful? Yes No