Dynamics CRM works largely on FetchXML queries. FetchXML is a proprietary query language for Dynamics CRM. For developers coming from SQL background, they would really want to find the SQL query that the FetchXML translates into. While one way is to explain the FetchXML syntax, it might help to also get the SQL statement that the CRM Platform converts into.
SQL Tracing is what comes to the rescue here. Using SQL Tracing, you can find all the SQL queries that get executed in the background when running CRM application.
Note: SQL Tracing can only be executed for CRM On-Premise systems.
Take an example where we have a below Fetch XML based query that is generated using advance find.
<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
<entity name=”account”>
<attribute name=”name” />
<attribute name=”primarycontactid” />
<attribute name=”telephone1″ />
<attribute name=”accountid” />
<order attribute=”name” descending=”false” />
<filter type=”and”>
<condition attribute=”createdon” operator=”last-x-years” value=”4″ />
</filter>
</entity>
</fetch>
Follow the below steps to get the SQL Query for the above.
- Open SQL Server Profiler and create a new trace.
To create a new trace click on File -> New Trace.
- Then start tracing by clicking on Run button.
- Open CRM environment and create an advance find for above query and click on Results button to execute that query.
- Then stop tracing and find the appropriate trace entry where you can see the SQL Query that was executed by CRM Platform in the background when we execute query from advance find.
In this way you will get the SQL query from the fetch XML.
exec sp_executesql N’select
top 51 “account0”.Name as “name”
, “account0”.PrimaryContactId as “primarycontactid”
, “account0”.Telephone1 as “telephone1”
, “account0”.AccountId as “accountid”
, “account0”.PrimaryContactIdName as “primarycontactidname”
, “account0”.PrimaryContactIdYomiName as “primarycontactidyominame”
from
Account as “account0” (NOLOCK)
where
(( “account0”.CreatedOn >= @CreatedOn0 and “account0”.CreatedOn <= @CreatedOn1 )) order by
“account0”.Name asc
, “account0”.AccountId asc’,N’@CreatedOn0 datetime,@CreatedOn1 datetime’,@CreatedOn0=’2010-05-03 07:00:00′,@CreatedOn1=’2014-05-03 11:52:49.553′
You can see from the SQL query, there is hardcoded date for condition i.e. Created on date on Last X Year.
There are built in SQL functions to query CRM data. In this case you can use “dbo.fn_LastXYear(<date>,<number of year>)”.
Conclusion:
Use this to understand the SQL query behind any CRM operation performed from the UI.
To generate FetchXML for a SQL query you have in hand you can use the online tool available at http://www.sql2fetchxml.com/ from KingswaySoft