Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Customer experience | Sales, Customer Insights,... / Query data Dynamics CR...
Customer experience | Sales, Customer Insights,...
Suggested answer

Query data Dynamics CRM on-premise VS dynamics CRM online

(0) ShareShare
ReportReport
Posted on by Community Member

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,331 Most Valuable Professional 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 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,331 Most Valuable Professional 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 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,331 Most Valuable Professional 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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Ramesh Kumar – Community Spotlight

We are honored to recognize Ramesh Kumar as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Announcing the Engage with the Community forum!

This forum is your space to connect, share, and grow!

Leaderboard > Customer experience | Sales, Customer Insights, CRM

#1
Holly Huffman Profile Picture

Holly Huffman 103

#2
Muhammad Shahzad Shafique Profile Picture

Muhammad Shahzad Sh... 96 Most Valuable Professional

#3
Gerardo Rentería García Profile Picture

Gerardo Rentería Ga... 51 Most Valuable Professional

Last month Overall leaderboard

Featured topics

Hide selecting Contact for potential customer when creating quote

Product updates

Dynamics 365 release plans