SQL Trace & FetxhXML

Dynamics CRM uses FetchXML queries. FetchXML is a proprietary query language for Dynamics CRM.
Developers with a SQL background, want to find the SQL query into which the FetchXML translates.
One way is to understand the FetchXML syntax, and you can also get the SQL statement with SQL Tracing to 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 advanced 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.
§ To start tracing click the Run button.
Open CRM environment and create an advance find for above query and click on Results button to execute that query.
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 you execute the query from advanced 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 see from the SQL query, there is a 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 use “dbo.fn_LastXYear(<date>,<number of year>)”.
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 use the online tool available at http://www.sql2fetchxml.com/ from KingswaySoft

Comments

Post a Comment

Popular posts from this blog

D365 F&O - Adding custom entities to Case Management

Image
Case management is a generic cross module solution where you can log cases with or without a defined process and keep a information. A case can be related to a number of various entities in the solution. There are 11 case categories where you can group processes: General Sales Purchase Service Project Production Collections Audit Human Resources FMLA Product Change You also have 27 entities you can associate the case with: None Lead Opportunity Customer Vendor Prospect Project Sales order Purchase order Worker Service order Item Customer transaction Expense report Expense report line Vendor invoice Vendor invoice line Purchase order line Returned order Case Product BOM/Formula Route Production order Quality order Customer invoice Customer invoice line Both categories and entities can be extended. We also added a lot of other entities to our standard expansion solution to our customers. For example, I created new entity 'Contra
Read more

Microsoft Dynamics CRM Time zones

Time Zones Dynamics CRM stored Date/Time fields in the database as a SQL datetime field that is always converted to a UTC date.  Each user has a Time Zone Code associated with their user settings. ·          To list all the available TimeZoneCodes use the following query against the MSCRM database: SELECT TimeZoneCode, UserInterfaceName FROM TimeZoneDefinition order by UserInterfaceName ·          To list all user's selected Time Zone Code use: Select SystemUserId, TimeZoneCode from UserSettings There functions available in the MSCRM database for converting to and from UTC to local dates. The following function accepts a utc date and converts it to a local date based on the time zone code matching those in TimeZoneDefinition. dbo.fn_UTCToTzCodeSpecificLocalTime(@utc,@timezonecode) Dates are stored as UTC Consider the following: ·          Joe is in the New York office and creates an appointment in CRM with a scheduled start of '26 Nov 2001 13:00'. ·
Read more

Dynamics 365 user/audit logs

Image
For new and current implementations of Dynamics 365 that require adherence to strict regulations such as FedRAMP and HIPAA, there are now powerful options available in the July 2017 update for Dynamics 365. These new features specifically address User Session Management, and Activity Log Management . To clarify for those slightly confused by the nomenclature of the latter, it does not specifically have to do with CRM Activity records, but is rather better described as functionality to replace the Audit Log. User Session Management Settings in Dynamics 365 Some government regulations and company policies require strict management of session timeouts in software for security purposes. In previous versions of Dynamics CRM, the session timeout for a user was generally configured to 24 hours, which does not meet these guidelines. Nevertheless, with the new User Session Management features in Dynamics 365 we can now easily configure within System Settings to change the timeout t
Read more