Exporting Note Attachnments / Annotations from Dynamics CRM 2015

Applies To

  • Microsoft Dynamics CRM 2015 Online
  • Microsoft Dynamics CRM 2015 Partner Hosted IFD
  • Microsoft Dynamics CRM 2015 On Premise - The process will be very similar but I've not tested

Client Request

I need to export and download documents I have uploaded and attached to the Notes entity in CRM 2015 Online. I need the files to be downloaded back to their original format. In Bulk.

The Issue

Getting a document back from a note in CRM is easy. Open the record, then open the note and click the link to get the file.

However, if you need to download a lot of them, it is going to take you a long time of clicking and opening to get them all.

Scenario

I have a client who works in the financial industry. Every so often they have an audit, where they need every document from a client, available to review. That means every single document and PDF needs to be exported into one folder for this client, so that the auditor can review them.

Related Information

  • The documents are stored in the SQL database as a binary BLOB.
  • They are stored in the Annotations table

Research

I spent over 30 hours, over a week looking for a solution. I thought I could pipe the data into an SSRS report and embed the files or somehow export the data from CRM, trying to use a beta edition of Scribe Software's File Stream addon - Nope.

Surely there must be an application out there to pull attachments. Well, yes, there is. During my scouring, I found two packaged options.

AttachmentExtractor for Microsoft Dynamics CRM

This tool looks like it would do the job - however whether there is any filtering on it for the records to limit what you get - i dont know. I didnt test the application, for the simple reason my client couldnt afford it.

Dynamics CRM 2011/13 Attachment and Entity data Extractor on Code Plex

I did try this, however I never actually got it working. I spent a few hours trying to get it to connect to my partner hosted CRM instance, but without success.

Solution

The solution I found (and assistance credit goes to Ray Murrell at Dominic Systems) involves a multi hop process using Azure, Scribe and Microsoft SQL Server. Sounds heavy, I know. I also need to give credit to the original "problem solution architect" over on Experts Exchange - Export CRM annotation attachments as files using classic VB

Some of these steps may be optional for you, if you already have them.

Solution Overview

Here's our end-game. We're going to use Scribe Software installed on a Microsoft Azure instance to connect to our CRM Online database and export the required attachments to a Microsoft SQL Express Database installed on the Azure server. Then use a funky VB Script to extract the documents binary, convert back into a real document and save to disk. When this has been completed, beer is optional.

Step One - Setup an Azure Instance

OK - it doesn't have to be an Azure instance. If you have your own Virtual Infrastructure, by all means, spin up a new VM. If you already have Scribe setup and installed then skip these steps and go use that. You could even install Scribe on your desktop/laptop. That's fine.

Step Two - Configure your MS SQL Express

Im using Express because I just need a SQL server to store the data temporarily and the express edition fits my needs.

Download and install the relevant SQL Express from Microsoft. By default picking this, has both the SQL Server and the SQL Studio management application.

You're going to use this for the Scribe installation too. TIP - you can skip this step entirely if you will be installing Scribe, as there is an option for SQL Install in the Scribe setup.

Step Three- Install and Setup Scribe on your New VM

Note - this requires a Scribe license. Hopefully you already have one or know someone with one. You can use the Scribe setup instructions, for a guide, but you can skip the parts about configuring message queues (if this is a test/quick install)

Step Three (And A Half)

You will need somewhere to put the exported documents. As I was doing a proof of concept and only need this for a short time, I just added a table to the SCRIBEINTERNAL database. You can add yours where you see fit.

Step Four- Configure your Scribe DTS for Export

Make a new DTS in the Workbench.

Connect to your Source - the SQL server.

Connect to the local SQL server for the Target.

Map the fields

NOTE: This screen shot came from the initial test - therefore table column names will not match.

NOTE: This screen shot came from the initial test - therefore table column names will not match.

Test The Transfer using the Test button and verify you are getting data

Run The Transfer and hopefully have no errors

Step Five - Add the Conversion Code

Now we need some code to convert the data we have and can easily access, back into a file stream when accessed. This function handles that for us on the SQL server.

The following code needs to be added as a function to the SQL server. Fair Credit again - this comes from the Experts forum.

Open the SQL Management Studio and select the correct Database

Run the following Script.

WARNING: This is the INTERNET. Do Not Trust This Script. Review it yourself before running someone else's code on your servers. I have run this code on my SQL server, but just because I did, doesn't mean you should. You have been warned.

CREATE FUNCTION [dbo].[DocBodyStream](@DocumentBody varchar(max))
--converts the DocumentBody field in CRM annotations table to a decoded stream
RETURNS varchar(max) AS 
BEGIN
DECLARE @decoded varbinary(max)
declare @stream varchar(max)
set @decoded = cast('' as xml).value('xs:base64Binary(sql:variable(''@DocumentBody''))', 'varbinary(max)')
select @stream=CAST(@decoded as varchar(max))
RETURN @stream 
END

This will add the new function to convert the binary file data.

Step Six - Create the VB Script

This is the magic script. It will connect to the SQL server, grab the file data and then save each document in

Dim cn
Dim rS
Dim sql
Dim fsO
Dim outPath
Dim objSubF
Dim Stream
Dim fOut
Dim oID
Dim AnnID
Dim i
Dim msg

'creates a subfolder under outPath for each object_ID and writes any
'available filestreams for the object into the folder.Ignores annotations
'where documentbody is null.

outPath = "h:\CRM\Images"

Set rs = CreateObject("ADODB.Recordset")
set fsO = createobject("Scripting.FileSystemObject")

cn = "DRIVER={sql server};Server=myservername;database=MyCRMSample;uid=myUID;pwd=MyPwd;"
sql = "select AnnotationId, ObjectId, MimeType, FileName,  DocumentBody, dbo.docbodystream(documentbody) as FileStream "
sql = sql & " from annotations where documentbody is not null;"

rs.Open sql, cn

Do Until rS.EOF
If IsNull(rS("objectid")) Then oID="None" Else oID=CStr(rS("objectid"))
AnnID=rS("annotationid")
objSubF=outpath & "\" & oID'subfolder path for object ID
If fso.FolderExists(objSubF)=False Then fso.CreateFolder(objSubF)
fOut = objSubF & "\" & rs("FileName")
If fsO.FileExists(fOut)=True Then fsO.DeleteFile fOut
Set Stream=fsO.OpenTextFile(fOut,2,True)
With Stream
.Write rS("filestream") 'rS("documentbody")
.Close
End With
i=i+1
'msg - a list of the annotation IDs and files created with size information.
'intended for small test runs. I had only 21 populated annotations.
msg=msg & vbCrLf & "AnnID: " & AnnID & "(table/disk): "
 & "/" &  " FilePath: " & fOut
rS.MoveNext
Loop

rs.Close

WScript.Echo "done! " & i & " documents created." & vbCrLf & msg


Copy and paste the script into a text file and save it with the VBS extension.

Ensure that you update the connection credentials and the file output folder.

You can then run the script, using wscript.exe <SCRIPTNAME>.vbs from a command prompt. If all goes well, you should have a success message and files in your output folder.





RunDLL Error/Crash when adding account to Outlook 2013

This morning I had a troublesome problem. It started out as a simple process of adding a new Exchange email account (from Office 365) to my Outlook 2013 client.

Every time I started the Add Accout wizard, it would crash, complaining about the RunDLL32 error - "RunDLL32 WINDOWS HOST PROCESS ERROR"

After a number of possible fixes, the solution was to Disable Hardware Graphics Acceleration.

  1. Open Microsoft Word 2013
  2. Go to File, Options, Advanced
  3. Scroll down to "display"
  4. Check the box "Disable Hardware Graphics Acceleration"

thanks to Rupert in this Blog Post

Dell Venue 11 Pro Freezing

Problem: Dell Venue 11 Tablet consistently freezing for about 30 seconds before resuming for another 30 seconds, then freezing again.

The tablet seems to recognize the touch input as the screen shows the swipe marks or the lightly shaded pressure points but nothing else.

Resolution: the problem lies with the Intel Rapid Storage system.

To disable this:

  • Press Start
  • move to the more apps area
  • open the Intel Rapid Storage application.
    • Note: Must be opened in elevated Admin mode
  • Select the Performance button
  • Press the Link Power Management option to disable it.
    • The option link should read 'Enable' when done.
  • Close the application
  • Restart the tablet.
    • This step is critical - without it, the LPM will remain on.

Shout out to: 'JacksRHard2Beat' on the Dell Forums for this post

Also, note that there is info on a recall for some Venue 11s. More details here.

Server 2008 R2 VM Guest on Hyper-V: vNIC defaults to Public Network instead of Domain

This morning, after a reboot, one of my 2k8VMs decided that it was no longer on the domain and couldn't find any domain controllers, therefore it defaulted to a Public Network for the NIC.

This had the unfortunate effect of rejecting all traffic sent its way.

I did some quick searching, but it appears that my Google-Foo is not strong at 8am before coffee and I came up blank.

I managed to resolve this by logging into the virtual console through Hyper-V manager and change the settings on the NIC to enable DHCP and grab an IP address. This immediately saw everything again and happily reported to be on the domain with internet access.

I then reset the NIC to a static IP (the same details as before), which it held and maintained its ability to talk to the domain.

Why? I dont know... anyone?

Windows 2008 R2 RDS Server Remote App Logs Off Immediately On Login

This morning i was presented with the perpetual log-off. When a user ran a remote app, the normal log in process would happen, however instead of the remote app appearing, it would then immediately log off the user.

I noted a system restart in the middle of the night for maintenance and reviewed the installed updates. One of these updates, was also mentioned on a StackExchange entry two hours prior by someone experiencing exactly the same issue.

If you are having the same problem, you may want to look into Microsofts KB3004394 update.

This issue, doesn't affect full remote desktop sessions - just the remote apps.

I have scheduled to remove the update this evening and test to see if this resolves the issue.

Stack Exchange entry: Rds RemoteApp logged off immediately after preparing desktop (under details) msi and rdp

Microsoft KB Entry: December 2014 update for Windows Root Certificate Program in Windows

Updating Internet Explorer on Windows Embedded Standard 7

Wow. This was a real pain.

I have two HP Thin Client T610 running Windows Embedded Standard 7. Both I need to use in a kiosk mode fashion for a client. Problem is, that the current version of IE installed on them, is version 8... a few years old.

Here's how I did it.

  1. Turn off the Write Filter to allow changes
  2. Enable the local Administrators account - even though I had a seperate local administrator account, it doesn't seem to have the privileges that the built in account does.
  3. Log in as the local Administrator account. (Hint: default password is: Administrator)
  4. Change the RAM DRIVE settings. This is the crucial, key part to the rest. By default, these little units had 32MB assigned to their Ram Drives for changes. 32MB! I adjusted this to 250MB.
  5. Install windows updates as needed
  6. Download the FULL INSTALL of Internet Explorer 11 to the thin client
  7. Run the installtion
    1. If needed, follow the link and install the pre-req's for IE11
  8. I also installed a PDF viewer at this point too.
  9. Disabled the admin account again.
  10. Re-Enabled the File Write Filter

Done!

Xen Server VM Export Fails

I have spent the last week or so, exporting VMs from a Xen 6 Server to a Hyper-V server.

All was going well, until I hit the last one. One of the Domain controllers.

As with every other server, I had shut down the server, opened the Xen Center console, right clicked and selected Export.

Except this time, it failed.

It took a few minutes to figure out, but the reason this failed to export, was because the server I had just powered off, was also the local DHCP server.

It turns out, that the Xen server spins up a hidden VM to do the export. During this spin up, it needs an IP address - which it couldnt get - and therefore failed to export the server.

Thankfully I had also installed the DHCP role on another server, so not too much time was lost.

 

Xen VM exported to Hyper-V has no internet connectivity

This weekend I began migrating some servers from a Xen Server to a Hyper-V server.

The process was pretty straight forward, as I had tested this, the week before, in the lab environment.

  1. using Xen Center, Power off the VM and export the server. Ensure to not select the 'one file' option.
  2. Open the export folder when complete, and copy (and rename) the vhd file
  3. Create the new VM on the Hyper-V server and attach the exported disk
  4. Power up
  5. Uninstall Xen Tools
  6. Reboot
  7. Install Hyper-V Guest tools
  8. Rub hands gleefully.

It was during the last phase that I ran into a roadblock. Obviously there was a change of hardware, requiring a reboot. The problem was, that once it was up, there was no internet connectivity. I was able to do anything on the LAN. I was also able to get dns lookups for external addresses, but I couldnt get traffic out of the LAN from this server.

The issue, it turned out, was that during the addition of the Hyper-V NIC, a persistent Route was added for 0.0.0.0 using the NIC as the gateway.

In a command window, use ROUTE PRINT to see your routing table.

the solution, was to remove this persistent route;

route delete 0.0.0.0

and re-add it with the correct gateway;

route add 0.0.0.0 mask 0.0.0.0 <gateway ip>

 

Powershell Cmdlets List

If you're like me, there are a number of websites you have bookmarked that you find useful, somewhere within a pile of hundreds of other bookmarks. So this article, outlines some useful Powershell cmdlets that I reference frequently enough to need them but infrequently enough to remember them.


  • Restarting and stopping a service on a remote computer:
Get-Service -Name bits -ComputerName <CopmuterName> | Set-Service -Status Running

Gotchya When Connecting to HyperV from Windows 8 Non Domain Computer

A short note that will hopefully save someone else the hours it took me to find this nugget on a Microsoft Forum.

When Connecting to a HyperV server, make sure that you are using a local account on the Windows8 machine, not the default "MicrosoftAccount" in the form of someone@somewhere.com. With the latter you will keep hitting an Access denied error when trying to remotely manage the firewall through the GUI.

Thanks to a3uit for pointing that out in the comments.

Configuring Remote Management Access for Hyper-V

[NOTE: This article still under development]

One of the big things about Hyper-V, is that although it is a Windows Server and capable of being domain joined, in some cases, you might not want to join a domain. (eg. a small business with one Hyper-V server. If the DC is on the Hyper-V server, then you may run the risk of not being able to connect to it, in the event that the DC guest doesn't start.)

In these cases, I prefer to leave the Hyper-V in a workgroup, however this makes managing it a little more interesting to configure.

Essentially the process is;

  • Install the Hyper-V management tool from the Windows Features or the Microsoft Windows Server RSAT
  • Add the HyperV server to the local client HOSTS file
  • Configure Clients Component Service
    • Start > Run > DCOMCNFG
    • In console open Components > Computers, > right click on ‘My Computer’.
    • Select the ‘Com Security’ tab and under ‘Access Permissions’ select ‘Edit Limits’.
    • Select the line that says ‘ANONYMOUS LOGON’, C
    • Check the box "Remote Access" under Allow.
  • Add credentials for the server using cmdkeyunder command prompt
    • cmdley /add:Servername /user:Username /pass:Password4ThatUser
  • on the server, enable Remote Management from the SCONFIG menu
    • Allow MMC Remote Managment
    • Allow PowerShell
    • Reboot Server
    • Allow Server Manager Remote Management
  • on the server, run the following firewall rule updates;
    • Enable-NetFirewallRule -DisplayGroup "Remote Administration"

Gotchya!

Be aware of this if using Windows 8 client (I spent hours troubleshooting this).

Make sure that you are using a local account on the Windows8 machine, not the default "MicrosoftAccount" in the form of someone@somewhere.com. With the latter you will keep hitting an Access denied error when trying to remotely manage the firewall through the GUI.

Thanks to a3uit for pointing this out on the Microsoft forums.


Other Useful Sites:

Configure remote management and setup HVRemote

http://blogs.msdn.com/b/virtual_pc_guy/archive/2010/11/11/configuring-remote-management-of-hyper-v-server-in-a-workgroup.aspx

Remote Management of Hyper V

http://social.technet.microsoft.com/Forums/windowsserver/en-US/fe80f0aa-0697-4657-a1da-19d36b036698/guide-to-remote-manage-hyperv-servers-and-vms-in-workgroups-or-standalone?forum=winserverhyperv


Credit:

http://www.peppercrew.nl/index.php/2014/02/managing-hyper-v-2012-r2-core-server-remotely-in-a-workgroup-environment/

http://www.peppercrew.nl/index.php/2014/02/managing-hyper-v-2012-r2-core-server-remotely-in-a-workgroup-environment/

http://blogs.msdn.com/b/virtual_pc_guy/archive/2010/11/11/configuring-remote-management-of-hyper-v-server-in-a-workgroup.aspx

Hyper-V PowerShell Commands

I just spent the evening working on a HyperV server, from the command line. I realized that I don't have a nice, one-place lookup for the comman commands I've been using. They are spread over a number of websites in my favourites bar. So, this is my one-stop reference for Hyper-V Powershell Commands. (Credit at the bottom)


Virtual Machines

New virtual machine

New-VM -Name Testmachine -path C:\vm-machine -MemoryStartupBytes 1024MB

New virtual disk

New-VHD -Path c:\vm-Machine\Testmahcine\Testmachine.vhdx -SizeBytes 60GB -Dynamic

Attach The Disk

Add-VMHardDiskDrive -VMName <name> -Path <path>

Add an ISO image to the Virtual Optical Drive

Set-VMDvdDrive -VMName <name> -ControllerNumber 1 -Path <path>

Add a new Network Adapter to the VM

Add-VMNetworkAdapter -VMName <name> -Name “Prod NIC” [-IsLegacy $true ]

Connect-VMNetworkAdapter -VMName <name> -SwitchName ‘Private Switch’

Set the Start policy for a VM

Set-VM -Name <name> -AutoStartAction Start

Virtual Machine Controls

Show all the VMs on the host

Get-VM

Starting it up:

Start-VM -Name <name>

Stopping the VM

Stop-VM -Name <name>

Take a snapshot of the VM

Checkpoint-VM -Name ProdServer* -SnapshotName PreMigrationSnapshot

Make a VM from a snapshot

Export-VMSnapshot -Name ‘PosUpdates’ -VMName NewVM -Path H:\NewVMfromSnapshot

 

Host Management

Add a virtual switch for the VMs

New-VMSwitch “External Switch” –NetAdapterName “Ethernet Connection” –AllowManagementOS $true

Get the RAM of the Host Hyper-V Server

(systeminfo | Select-String 'Total Physical Memory:').ToString().Split(':')[1].Trim()

Credit:

  • http://stackoverflow.com/questions/17681234/how-do-i-get-total-physical-memory-size-using-powershell-without-wmi
  • http://www.thegeekstuff.com/2014/02/create-vm-from-powershell/
  • http://andersonpatricio.ca/performing-daily-task-with-powershell-to-manage-hyper-v-server/

Configure Remote Desktop for Hyper-V Core 2012

This evening while configuring a new Hyper-V Core Server 2012, even though I enabled the Remote Desktop option in the Config console, it still wouldnt play nice.

So todays tip - dont forget to enable the firewall rule. (Thanks to this post on Tech Net by Adnan Bhatti)

"you have to enable a rule in the firewall. Just type below in command prompt after enabling the RDP through CLI. "

netsh advfirewall firewall set rule group="Remote Desktop" new enable=yes

Running an SXS (Side By Side) Stack Trace

You can read the Microsoft TechNet sheet on SXSTrace here

1. Open an Administrative Command Prompt Window

2. Start the trace using:

sxstrace.exe Trace -Logfile:sxslog.log

The sxslog.log file will be placed in which ever folder you execute the program from.

3. Run the program you are having trouble with.

4. Stop the trace by pressing ENTER.

5. Convert the trace to something you can read:

sxstrace.exe Parse -logfile:sxslog.log -outfile:sxslog.txt

6. Read the contents and fix your problem!


As an alternative to leaving the command window open, you can also specify that it should run in the background by:

2. Start the trace using:

sxstrace.exe Trace -logfile:sxslog.log -nostop

4. Stop the trace by using:

sxstrace.exe Stoptrace

 

Dell Latitude E7240 - SSD Location

Today I was called on to help locate an SSD for a users laptop upgrade. After some minor disassembly, we realized that the E7240 is equipped with a mSATA SSD (mini-SATA) more here at Wikipedia.

The actual location of the drive can be found underneath the main cover on the bottom of the device. 

Remove the battery, then remove the two holding screws.

There is also a second mSATA bay. Ive not done any research on this yet, but it looks to be an expansion bay for additional storage.

CRM 2011: Locate workflows triggered by a specific field

Today I was trying to track down which workflows were fired on modification of a specific field. This info is all stored in the WORKFLOW table in the SQL DB, so the following SQL Scrap was enough to locate the offending workflow.

declare @FieldNamePartial as varchar(250)
select @FieldNamePartial = '<FIELDNAME>'

SELECT Workflow.ActiveWorkflowIdName AS WorkFlowOnField, Workflow.TriggerOnUpdateAttributeList AS TriggeredByField, EntityLogicalView.Name as EntityName
FROM Workflow LEFT OUTER JOIN
EntityLogicalView ON Workflow.PrimaryEntity = EntityLogicalView.ObjectTypeCode
WHERE (Workflow.TriggerOnUpdateAttributeList LIKE '%' + @FieldNamePartial + '%')

Just replace the <FIELDNAME> with part of the field you are looking to find.

As with any code you find on the internet - use at your own risk.

CRM 2011 Finding Workflow that triggers a specific workflow

I recently had to troubleshoot a CRM workflow. Unfortunatley, it was set to 'Run As Child', meaning it could have been called from any one of a number of workflows.

I drafted this little SQL snippet to find any mention of the child workflow, in the XAML field of all the other workflows. 

declare @WorkflowNamePartial as varchar(250)
select @WorkflowNamePartial = '16-4'

declare @WorkflowID as varchar(250)
select @WorkflowID = WorkflowId FROM [Workflow] where ActiveWorkflowIdName like @WorkflowNamePartial +'%'
SELECT ActiveWorkflowIdName As ProcessesTriggeringSelectedWorkflow FROM [Workflow] where XAML like '%' + @WorkflowID + '%'

Simply change the second line to the name of the childworkflow and run against the MSCRM database.