Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics CRM forum
Suggested answer

Query data Dynamics CRM on-premise VS dynamics CRM online

Posted on by Microsoft Employee

Objective

Hi, I'm currently working with dynamics CRM on-premise where I'm directly connecting to the SQL server database and fetching data through SQL queries.

Since on-premise features are gonna end-up deprecated soon, I'm planning on using a dynamics CRM online  environment. 

Research

In my research I ended up in the documentation of "Dataverse", I setup a sandbox environment where I can query data through the web API.

However I can't figure out how to access SQL views (or something similar to predefined queries). They are not defined in the API index (or endpoint list as I like to call it).

Looking to implement custom SQL views  in dynamics CRM online

I then read about "savedquery". It seems it used to store a predefined SQL query. I successfully created some and executed them, but it seems some EntityType are not accessible through saved queries...

What can I do?

I want to replace my hundreds of SQL queries I have already written to make them compatible in a dynamics CRM online environment.

Some of those SQL queries are SQL views and I can't seem to rewrite them using fetchxml (language/template used to use saved-queries).

Am I on the right track? Am I clear on what I want to achieve?

I look forward to any advice, thank you.

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,313 Super User on at
    RE: Query data Dynamics CRM on-premise VS dynamics CRM online

    Unfortunately, you can't create views, stored procedures, or functions. What you can do is to build queries to fetch the data using Select/From/Case statements. Temporary tables are not available as well.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Query data Dynamics CRM on-premise VS dynamics CRM online

    Can I create custom SQL views or any type of predefined queries to fetch pre-structured data?

    I have SQL views using the CASE statement and IF THEN ELSE. Could I replicate those somehow or my only choice is to use the api?

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,313 Super User on at
    RE: Query data Dynamics CRM on-premise VS dynamics CRM online

    That SQL Access is Read-only. In order to perform Create/Update/Delete you will have to use WebApi.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Query data Dynamics CRM on-premise VS dynamics CRM online

    Any way to enable full permissions to execute CRUD sql queries for dynamics CRM online? I'm using SQL views in my on-premise setup and I can't find a way to replicate those with the provided web-api. I'm not sure if it would be wise to use the web-api for simple update/create/delete request and use SQL to query data with read-only to achieve the behavior as my views, but without views...

  • Suggested answer
    a33ik Profile Picture
    a33ik 84,313 Super User on at
    RE: Query data Dynamics CRM on-premise VS dynamics CRM online

    Hello,

    It's possible to use T-SQL to query data from Dataverse. Check this article - docs.microsoft.com/.../dataverse-sql-query

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 288,420 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,672 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans