You’re offline. This is a read only version of the page.
We've created a series of document templates (MS Word) over the course of a few weeks. In the last day or so the templates have begun throwing an error when run. A screen shot is shown below.
A SQL trace has enabled us to isolate the cause of the error to a sql script that fails with the description "The data type image cannot be used as an operand to the UNION, INTERSECT or EXCEPT operators because it is not comparable".
The trouble is we don't know what caused this issue or how to correct it.
*This post is locked for comments
It seems like this is fixed in the Microsoft Dynamics 365 Update 2.1 - although I can not find any documentation on this.
support.microsoft.com/.../microsoft-dynamics-365-online-and-on-premises-update-2-1
Does anyone else have the same experience?
We completed our support case with MS and was about to update this post when we noticed your update. This fix does work. We are trying to get more details regarding all OrgDB settings and if there are any best practices regarding setting/using this value and others.
Thank you Jason for this Solution!
I've tested it and it works just as expected
Hi Serban, Microsoft resolved this issue for us. Thanks for your help, read article
community.dynamics.com/.../enableretrievemultipleoptimization-part-1
Hello Jason,
I have the same problem as you do. The only thing that apparently fixes the issue is giving Organization Read on Contact Entity. This isn't an option for me neither.
I traced the SQL scripts and that "image" is the EntityImageId field. When you export word template, at some point of the mechanism, the SQL runs a security check on Contact entity. The SQL expression looks something like this:
exec sp_executesql N'WITH "contact0security" as (
select SpousesName as "SpousesName", FirstName as "FirstName", [...], EntityImageId as "EntityImageId", [...], EntityImage_Timestamp as "EntityImage_Timestamp", EntityImage_URL as "EntityImage_URL", EntityImage as "EntityImage", [...], ModifiedOnBehalfByName as "ModifiedOnBehalfByName" from [Contact] as "contact0" where "contact0".OwnerId in (@userPrincipalWithBasicDepth00)
UNION
select SpousesName as "SpousesName", FirstName as "FirstName", [...], EntityImageId as "EntityImageId",[...], EntityImage_Timestamp as "EntityImage_Timestamp", EntityImage_URL as "EntityImage_URL", EntityImage as "EntityImage", [...], ModifiedOnBehalfByName as "ModifiedOnBehalfByName" from [Contact] as "contact0".ContactId in (select ObjectId from fnPOARetrieveMultiple(@SystemUserId0, @ObjectTypeCode0, @isHierarchicalSecurityModelEnabled0))
)
select top 100 "contact0".SpousesName as "spousesname"
, "contact0".FirstName as "firstname"
, [...]
, "contact0".EntityImageId as "entityimageid"
, [...]
, "contact0".EntityImage_Timestamp as "entityimage_timestamp"
, "contact0".EntityImage_URL as "entityimage_url"
, "contact0".EntityImage as "entityimage"
, [...]
, "contact0".ModifiedOnBehalfByName as "modifiedonbehalfbyname"
from
contact0Security as "contact0"
where
(("contact0".ContactId = @ContactId0))',N'@ContactId0 uniqueidentifier,@userPrincipalWithBasicDepth00 uniqueidentifier,@SystemUserId0 uniqueidentifier,@ObjectTypeCode0 int,@isHierarchicalSecurityModelEnabled0 bit', (and now comes the values of the parameters)
Hi Serban,
Thanks for the response. We are using the standard functionality of MS CRM 2016, Word Templates but have not modified any SQL scripts we are merely tracing the scripts using SQL profiler.
Unfortunately giving Read privileges to the Contact entity is not an option for us as this will break the security privileges for our users.
Have you come across any other options?
The script is created by Microsoft CRM when the document template is generated. We have no control over it including no control over the data type (image) for the field EntityImage.
Hello PG,
I am guessing that Jason used the standard functionality of MS CRM 2016, Word Templates.
It is not supported by Microsoft to modify SQL scripts.
Hello Jason,
Right now I have the same problem. To overcome this error, with lots of trials and errors, I gave the security role Read privilege at Organization level on Contact entity.
I don't want to this, and I'm also looking for an answer.
Hello Jason,
I would suggest to use VarBinary instead of the Image datatype. Do you have control over the script then you can modify it? Image data type is deprecated.
Best Regards
Pankaj
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,240
Super User 2024 Season 2
#2
Martin Dráb
230,149
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (