Advertisements

{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

Capture

 

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.

image

 

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

image

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.

image

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

image

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

image

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.

image

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

When done it will show a message like below screenshot.

image

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.

image

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.

image

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

image

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

image

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’

image

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.

Advertisements

About Debajit
I am a Dynamics CRM Most Valuable Professional (MVP) with 10 years of experience in Microsoft .NET Technologies and 7 years of dedicated experience in Microsoft Dynamics CRM. I have worked with companies like Microsoft, SanDisk, PwC, TMF Group and have extensive experience of implementing complex CRM solutions from both offshore and client side. Currently the face of XrmForYou.com with significant experience in delivering corporate training on Dynamics CRM and have already delivered multiple projects to client through XrmForYou.com Author of multiple tools on codeplex including the 'Role Based Views' and 'CRM-Sharepoint Metadata manager & Attachment Extractor' which are available for commercial use under XrmForYou.com For consulting/ training, drop me a note at info@xrmforyou.com or visit our website www.xrmforyou.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: