{Dynamics CRM + Fetchxml + SQL} FetchXml to SQL converter for Dynamics CRM 2016/ 2015. Enjoy seeing your fetchxml queries getting converted to SQL without using profiler

First of all, I have been able to make this work for CRM 2015/ 2016 on-premise and IFD but does not work with online version. However I am still trying hard to make it work for online version as well. Hopefully I would be able to find a solution very soon.

Now coming to what the tool does.

1. First of all, it is not a custom logic of string manipulation which is based on the format of FetchXml. So even if the fetch XML format changes in a rollup update, it would still work
2. It would generate exactly the same SQL that would find if you check in your profiler, for your fetch calls.

Now coming to the requirement

How often does it happen that a view is taking significant amount of time to execute (both personal and system views) and you wish to view what is the SQL query generated behind the scenes. And sometimes you have constructed this fetchxml from advanced find and using it in your plugins and workflows and somehow that is taking a lot of time to execute. You wish if I could have the SQL for it to check what exactly is happening behind the scenes.

And below are the steps that you would normally do to view SQL query

• Login to the database server (assuming you have access)
• Launch the profiler
• Run you view/ custom fetchxml
• The profiler would generate lot of logs.
• From the log list, find out the query for you trigger

We have all done it I guess. But you have two problems here

• Assuming you have access to the database and launch the profiler, still it is a considerable effort to every time start a new session and take out your query from the list of logs  generated.
• Second and perhaps the most important point here. You can use this tool even if you do not have login to the database server. Are you guessing that you still need to have System Administrator role in Dynamics CRM to use this tool? Well then I have to disappoint you here. Even if you are a sales user but still want to peek what’s going on behind the scenes, it will allow you to do so.

My team was having these repeated complaints about this and I thought why not give a weekend for this. 🙂

Download links

The tools is available for download at – FetchXml to SQL Converter

You would need to go to the Downloads section and download the version as per your CRM version

Documentation:

Once you download and unzip you would find two stuffs

• Folder containing the executables
• A managed solution.

I have using the 2016 version and this is what I see after I unzip.

The next step is to import the managed solution. Don’t worry. It is a very simple solution and it won’t even touch any of your existing entities any way.

Once the import is successful, open the executable folder and run the FetchXmlToSql.exe

The first thing it would ask for is your organization type. I have on-premise and hence I select the appropriate choices and it guides me to enter the details.

Once you enter your password and press enter, the system would search for all the organizations you have access to and show you.

I have only one. So I put ‘1’ as input in the console. If you have multiple organizations configured, all would be displayed here.

Once I select the appropriate organization and press enter, I am presented with the below choices

1 – Press 1 if you have any fetchxml saved in a file. Ideally you would use it in case you construct an advanced find query and download the fetchxml. In case you have some custom fetchxml, save it in a file. The moment you press enter, it would ask you to select a file.

2 – Using this option allows you to view the SQL query for your system views configured for an entity

3 – Using this option allows you to view the SQL query for your personal views you created for an entity.

FetchXml Input:

I enter 1 and then press Enter. It asks me to select a FetchXml file. I select a fetchxml that I downloaded from advanced find to view the enabled users for my organization.

After the file is selected, it takes some time to generate your SQL.

When done it will show a message like below screenshot.

The output file is generated in the same location as you executable. When I click open the file, I could see the same SQL being generated like the one you are familiar viewing in advanced find.

Liking it. Well then let’s explore the other options as well.

Saved Views:

I enter 2 this time and then you are prompted to enter the entity schema name. Please note you have to enter the entity logical name of the entity for which you want the system views to be displayed and not the display name. I enter opportunity.

When I press enter, I could see all the views configured in my system for opportunity.

As you can see that it displays all the system views configured for opportunity with a number for each. All you need to do is enter the number corresponding for the view you want to proceed with. I want to view ‘All Open Opportunities’. So I press 39 and then press enter

As it says, it saved to some file. I open the file and this is query generated below.

select  “opportunity0”.sandisk_OpportunityID as “sandisk_opportunityid”
, “opportunity0”.sandisk_opportunitytypeName as “sandisk_opportunitytypename”
, “opportunity0”.CreatedOn as “createdon”
, “opportunity0”.sandisk_OppCloseDateFiscalQuarterName as “sandisk_oppclosedatefiscalquartername”
, “opportunity0”.sandisk_RSMName as “sandisk_rsmname”
, “opportunity0”.sandisk_OpportunityValue as “sandisk_opportunityvalue”
, “opportunity0”.sandisk_regionName as “sandisk_regionname”
, “opportunity0”.OpportunityId as “opportunityid”
, “a_ef8066bb4eb040ba856b83c0ae259e29”.StageName as “a_ef8066bb4eb040ba856b83c0ae259e29.stagename”  from  FilteredOpportunity as “opportunity0” left outer join FilteredProcessStage as “a_ef8066bb4eb040ba856b83c0ae259e29” on (“opportunity0”.StageId  =  “a_ef8066bb4eb040ba856b83c0ae259e29”.processstageid) order by  “opportunity0”.name asc

Wow. isn’t that easy?

Personal Views:

Follow the same steps as Saved Views. Except you should chose 3 instead of 2 as input.

Now coming to another important part. The above would happen smoothly for a user who is a system administrator. However if you are a normal user, running it simply would throw an error. Don’t worry. I have you guys covered here.

The system administrator needs to open the security role you are having and then go to custom entities and then search for the entity named ‘Target Entity’

Provide access to this entity. And you are done.

The next time you run the same, you could see the the tool in action.

Leave me a comment if this tool really helped you.

If you wish to donate, you could do so at my paypal account debajit.prod@gmail.com. Your comments and support gives me the inspiration to keep me going.