Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


Migrating Notes via Scribe Failing in Dynamics CRM

Post Author: Joe D365 |

Are you using Scribe Insight to migrate Notes (annotations) and their associated attachments, but noticing the attachments are getting corrupted in the process? If so, then you may have just found the answer you've been looking for.

Here are a couple of the errors that we've noticed depending upon other file type:

"Windows Photo Viewer can't open this picture because the file appears to be damaged, corrupted, or is too large."

"We're sorry. We can't open xxxx.docx because we found a problem with its contents."

Additionally, some attachments open within a browser showing some html code similar to this:

- <error xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<exception>Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=6.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: System.Web.HttpUnhandledException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #C2BB0118Detail: -2147220970 System.Web.HttpUnhandledException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #C2BB0118 2014-06-02T22:03:32.0499118Z -2147220970 System.FormatException: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #F08D964A 2014-06-02T22:03:32.0499118Z exception>

<parameters xsi:nil="true" />

<displaytitle />

<displaytextencoded />

<displaytext />

<description>An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.description>

<file>Not availablefile>

<line>Not availableline>

<details>Exception of type 'System.Web.HttpUnhandledException' was thrown.details>

 <traceInfo />

 <requesturl>https://crmdev.xxxxx.com/Activities/Attachment/download.aspx?AttachmentType=5&AttachmentId=C6377CD3-A709-E311-9B03-005056A16BC5&IsNotesTabAttachment=null&CRMWRPCToken=bTPgMuNYEeOVEABQVqECXyU0EYquBVyhgELq9IQHk01A8tuBHXmnCavRSzACkXn&CRMWRPCTokenTimeStamp=635373434074634530requesturl>

<pathAndQuery>/Activities/Attachment/download.aspx?AttachmentType=5&AttachmentId=C6377CD3-A709-E311-9B03-005056A16BC5&IsNotesTabAttachment=null&CRMWRPCToken=bTPgMuNYEeOVEABQVqECXyU0EYquBVyhgELq9IQHk01A8tuBHXmnCavRSzACkXn&CRMWRPCTokenTimeStamp=635373434074634530pathAndQuery>

 <source>XMLsource>

 <stacktrace />

 error>

We've uncovered what appears to be a bug in the most current ODBC SQL Server Native Client drivers. If you are connecting directly to the CRM SQL Database as the source, it is recommended to use the Native SQL connection as shown here.

Migrating Notes via Scribe Failing in Microsoft Dynamics

The native SQL connection requires an actual SQL user. If you are using an AD Account, you'll need to create an ODBC connection. This is where the bug has been uncovered regarding attachments. Do not use the two ODBC drivers selected in yellow below. You'll need to use the standard SQL Server driver noted in red.

Migrating Notes via Scribe Failing in Microsoft Dynamics

Using the Native SQL ODBC drivers will likely result in corrupted or unreadable file attachments.

In addition, in order to use the SQL Server driver shown above, you'll want to be sure to have the DocumentBody be the last field in your source query. Something similar to the query below will get you the Owners name as well as the other fields necessary to migrate this data.

 SELECT [AnnotationId]
,[ObjectTypeCode]
,[ObjectId]
,[Subject]
,[IsDocument]
,[NoteText]
,[MimeType]
,[LangId]
,n.[CreatedOn]
,[FileSize]
,[FileName]
,[OwnerId]
,u.fullname
,n.[ModifiedOnBehalfBy]
,[OwnerIdType]

,[DocumentBody]
FROM
[AnnotationBase] AS N
INNER JOIN SystemUser AS u on u.systemuserid = n.ownerid

  As you've likely found out already, you need to be sure you are using the correct combination of Virtual Fields for this type of migration to work correctly.

Here are the correct field mappings if you're using a SQL Query as your source. In this example, we're migrating Notes and their attachments from CRM2011 to CRM2013 OnPremise.

Migrating Notes via Scribe Failing in Microsoft Dynamics

First, notice that DocumentBody is the last field to show in the source.

Second, be sure that if the ObjectTypeCodes for any custom entities are not the same in your source as they are in the target, you'll likely need to use some nested IF statements or a cross-reference table to populate this field.

Third, notice the two Virtual Fields (vf) that are mapped for the attachment.

Please note that if you are using the Dynamics CRM Scribe Adapter to connect to your source, then you'll need to use a different set of virtual fields.

For additional troubleshooting ideas, visit our myriad of blog posts. As always, if you need additional assistance with Dynamics CRM, don't hesitate to reach out to PowerObjects.

Happy CRM'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

PowerObjects Recommends