Often, when working with the latest features as part of Dynamics CRM/Dynamics 365 for Enterprise (CRM/D365E), it very much feels like handling a double-edged sword. Whilst the functionality they bring to the table is often impressive, there is typically scant information available when things go wrong – either through official channels or via online forum/blog posts. This can often act as a major barrier to early adoption, particularly when the business benefit of such adoption far outweighs any wasted time or extraordinary effort.

One of these new features is Word Templates, a feature that I have blogged about previously – particularly in how they compare against the more traditional SQL Server Reporting Services (SSRS) Reports that CRM/D365E offer developers. They present CRM customisers a much more familiar and equally powerful means of creating common templates that can be run against specific CRM record types, providing largely similar functionality compared to SSRS Reports. For those who have not been fortunate enough to have previous experience using SSRS, Word Templates present a far more accessible and friendly approach to addressing documentation needs within CRM/D365E.

When configuring access to Word (and indeed Excel) Templates for your users, you generally only need to be concerned with two set of permissions – adequate level Read permissions against the entity that the template is being run against (Lead, Account etc.) and the Document Generation privilege, in the Business Management area on the Security Role window:

When we were recently attempting to setup access to Word Templates for a specific group of users on CRM 2016 Online (8.1), we first verified that all of the above privileges had been granted – but we still encountered a strange issue when attempting to generate the Word Template:

This message should be familiar to those who work with the application frequently, as it generally is one of the error types that is generally thrown back when a database error has occurred – typically a timeout error or similar. What is distinctly not familiar about the above is the fact that we are unable to select the Download Log File button. Doing so would generally present us with a sufficiently detailed error message about the underlying problem. Without this, the issue becomes significantly more tricky to diagnose.

Before escalating the issue further with Microsoft. we were able to diagnose and observe the following:

  • Users that had been assigned the System Administrator security role were able to generate the template without issue
  • Users within the root Business Unit, likewise, had no issue generating the template
  • We were unable to replicate the issue within a separate environment, that had been configured the exact same way (same Business Units, Security Roles etc.)
  • When running a Fiddler trace whilst reproducing the issue, nothing additional error message wise was exposed behind the scenes. Fiddler, for those who are unaware, is one of the best tools to have in your arsenal when diagnosing web service request issues in CRM. As the act of generating a Word Template would (presumably) cause a web service request, it was hoped that something additional clues could be gathered by using Fiddler.

Due to the limitations of CRM 2016 Online compared with On-Premise CRM 2016 (i.e. we had no way in which we could interrogate the SQL database for the instance), we had to escalate the case to Microsoft in order to provide a resolution. And, as is generally the case in these matters, the proposed solution was informative and surprising in equal measure.

The support engineer assigned to the case took a copy of our instance database and deployed into a test environment, to see if the issue could be reproduced. They also have the benefit of being able to access information regarding the instance that I would imagine most CRM Online administrators would give their right hand for 🙂 Because of this, we were able to determine the underlying error database error message that was causing the problem:

The data type image cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable

The error is currently an acknowledged bug in CRM, which is targeted for resolution early in 2017. In the meantime, the engineer pointed me towards a tool that I was previously unaware of – the Dynamics CRM Organization Settings Editor. I was already aware that there are a number of settings relating to a CRM/D365E organisation that can be modified via PowerShell/cmd line executable for On-Premise deployments only. These settings can achieve a number of potentially desirable changes to your CRM Organisation, some of which cannot be achieved via the CRM interface alone – such as changing whether emails are sent synchronously or asynchronously (SendEmailSynchronously) or modifying the number of elements that are displayed in the tablet app, such as fields (TabletClientMaxFields), tabs (TabletClientMaxTabs) and lists (TabletClientMaxLists). In order to make these changes, there is a tool that you can download from Microsoft that enables you to change these settings via a cmd line window – but, arguably, the Dynamics CRM Organization Settings Editor is a far simpler tool to use, given that it is a managed solution that enables you to modify all of the settings from within CRM/D365E. Regardless of which tool you use, the solution suggested by the support engineer (which resolves the problem, incidentally) is outlined below, using the Dynamics CRM Organisation Settings Editor. It is worth noting that, although the KB article above does not specifically reference this, I was advised that using the tool is unsupported by Microsoft. Therefore, any changes – and issues that may be caused as a result – are made at your own risk:

  1. Install the managed solution file into CRM and then open up the Solution. You will be greeted with the Configuration page, which should look similar to the below:
  2. Navigate to the EnableRetrieveMultipleOptimization setting, which should be set to the default of not set.
  3. Click Add to modify the setting. You will be asked to confirm the change before it will take effect:
  4. Once you click OK, the default value of 1 will be applied to this setting. Fortunately, this is the precise setting we need to get things working, so verify that this is indeed set to 1 on your instance and then close out of the solution:

Now, when you refresh CRM for the affected user, you should be able to download the Word Template without issue.

Although we can expect this error to be resolved shortly as part of the next update to D365E, hopefully, the above workaround will help others who come across the same issue and allow you to use Word Templates without issue within your CRM/D365E environment 🙂

4 thoughts on “SQL Server Error When Downloading Word Template (Dynamics CRM 2016/Dynamics 365 for Enterprise)

  1. I’m having exact same issue but not with Word templates but with Email templates. I tried your fix but it didn’t help.

    Can you tell me more about the setting you changed. What is this setting?

    Thanks

    • The CRM Chap on 21st April 2017 at 8:33 pm said:

      Hi Steeve,

      The EnableRetrieveMultipleOptimization is a setting that lets you specify how certain backend SQL Server queries are compiled and executed. So in this example, setting it to 1 creates a temporary table that is then populated with all the records that you have access to and then filters to return the data you need. There’s a really handy article from Jarrod Williams that provides a far better summary of the setting than I possibly could:

      https://community.dynamics.com/crm/b/dynamicscrmsupportblog/archive/2012/10/22/enableretrievemultipleoptimization-part-1

      Sorry to hear that you’re having the same problem with Email Templates. I would suggest tinkering around with the different values of this setting in the first instance to see if that does anything. Failing that, and given that it is already an acknowledged issue for Document Templates, your next step would be to contact MS support.

      Hope this helps!

      Regards

      Joe

  2. Anish Thakur on 2nd May 2017 at 1:03 pm said:

    Hi Joe,

    I came across the same issue.

    User with specific security role was not able to download the word template even that security role was given all the required privilege in all the in scope entities being used in that template.

    When I increased the privilege of READ on ACCOUNT at ORG level (before it was at USER level) to that security role, it was working well.

    We raised this ticket to Microsoft and they said it is bug, later on with latest update in that CRM online 2106, it got fixed and now that security role is working well with the READ privilege on USER level for Account.

    Shared my experience, might help anyone else.

    Thanks,

    • The CRM Chap on 2nd May 2017 at 1:19 pm said:

      Hi Anish,

      Thanks for sharing your experience and glad you got it working in the end. It’s always important to involve Microsoft support when these issues occur, so a speedy fix can be rolled out.

      Regards

      Joe

Leave a Reply

Your email address will not be published. Required fields are marked *

Post navigation