Use SQL Tracing to find the SQL query generated for Dynamics CRM Platform Operations for CRM On-Premise

By | May 5, 2014

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.

img1

To  create a new trace click on File -> New Trace.

img2

 

  • Then start tracing by clicking on Run button.

img3

  •   Open CRM environment and create an advance find for above query and click on Results button to execute that query.

img4

  •  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.

img5

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

Category: Dynamics CRM 2013 Tags: , , , ,

About Inogic

Inogic is one of the most renowned Microsoft ISV with a Gold competency in Microsoft Dynamics CRM ecosystem. Inogic is a hub of innovation, and cutting-edge technologies delivering innovative services and solutions like Maplytics™, one of the first geo-analytical mapping solutions to be Certified for Microsoft Dynamics (CfMD) & InoLink: QuickBooks Integration, to CRM Customers and Partners alike.

Leave a Reply