Export CRM annotation attachments as files using classic VB

Posted on 2014-07-18
Last Modified: 2014-07-27
I am preparing to receive data extracted from a Microsoft CRM database (SQL2008r2) which contains file streams stored as base64 encrypted BLOB data.   I haven't had to work with BLOBs before and need to find a simple way to extract these to files.  I'm mostly a MSSQL developer (so T-SQL) with VBA/vbscript and some classic ASP experience.  

The few code samples I've been able to find are from .Net thin clients, and focus on  loading individual attachments to a web page.  What I need to do is extract all of the BLOB fields directly to document files.  

The pertinent fields are:

DocumentBody    varchar(max)
FileName     nvarchar(255)
MimeType  nvarchar(256)

I was able to get a small sample of this table to work with, which does not contain all the mime types in the database, but it's enough to get started with.  I don't know if the mime type is important if I have the filename with correct extension, but here are the types in my sample:


Since I'm under a time constraint, experimentation with .Net/aspx is not really feasible.   I'm also somewhat more confused by the fact that typically BLOBs are stored in varbinary fields and in CRM they are stored as varchar(max).  Since most of the examples I can find for dealing with base64 encoding deal with binary data, translating that to the CRM situation presents another challenge.

If you have experience with CRM data and converting/exporting CRM attachments to files, I'd really appreciate your insights.

Thanks in advance!
Question by:jaw0807
  • 6
LVL 19

Expert Comment

by:Rikin Shah
ID: 40206109

First thing, accessing CRM database directly is unsupported. You can access the data via Microsoft Dynamics CRM SDK.

I suggest you create a .net application which uses SDK messages to fetch files from annotations.

Let us know which version are you working on currently?
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40206431
I suggest you create a .net application which uses SDK messages to fetch files from annotations.
This was suggested in the author's previous duplicate question here:

unfortunately there response was to request that the thread be deleted with the comment:
The answer provided wasn't applicable to my problem, and I am going to resubmit the question since it seems identifying/categorizing the source database as Microsoft CRM will yield more useful answers.

No other feedback was provided.

Author Comment

ID: 40206503
My organization will be receiving only a partial extract of the CRM data. We will not have long-term access to the CRM client that currently serves it (nor do we own, or want to own, CRM).   We are required to take the extracted data for archival purposes and need to be able to extract the documents, but the people who are required to give us the data are not willing to extract the documents for us nor give any but the most minimal support on our getting even the data.  And there is not enough time for me to branch out into .net development to try and resolve this problem.  I could probably manage to make a more fully fleshed out example work but I don't have enough time on this to figure out how to incorporate tiny snippets.  I'm not building an interface right now, just trying to get the annotation attachments for all the extracted records into documents.
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  


Author Comment

ID: 40206653
Never mind. I downloaded Motobit's ScriptUtils and was able to do exactly what I needed using the ByteArray class.

Author Comment

ID: 40212841
I've requested that this question be closed as follows:

Accepted answer: 0 points for jaw0807's comment #a40206653

for the following reason:

Motobit had a sample ASP page for the ByteArray class that was able to convert the contents of a CRM DocumentBody field to a recognizable PDF stream.  They had ready examples on their site for VBScript functions that convert from Base64, and I simply looped through my recordset and wrote the resulting streams to file using FileSystemObject.    I'm giving this solution a C because the problem was basically resolved with a purchase and I am sure there was a free alternative somewhere, but what I bought allowed me to attain the objective I outlined in the question, and using the platform I described, quickly and easily.

Accepted Solution

jaw0807 earned 0 total points
ID: 40212823
Actually, there was a simpler and more efficient way.  

Found the basics on some built-in SQL functions for Base64 here:
Tinkered with that until I got a result that resembled the streams output by the aforementioned Motobit site.  Here is the T-SQL for the custom function.

CREATE FUNCTION [dbo].[DocBodyStream]
(@DocumentBody varchar(max))
--converts the DocumentBody field in CRM annotations table to a decoded stream
RETURNS varchar(max) AS 
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 

Open in new window

This vbscript creates subfolders with the ObjectID (desirable for what I'm doing) and then writes any annotations for that ObjectID to the appropriate folder.

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, FileSize, 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"))
	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")
    End With
   	'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 & "  Sizes(table/disk): " & _
		rS("filesize") & "/" & fsO.GetFile(fOut).Size & " FilePath: " & fOut 


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

Open in new window

I prefer this since it uses only built-in functionality, as well as being faster.

Author Comment

ID: 40212842
Would like to change the accepted solution from ID: 40206653 to ID: 40212823, since it was an improvement on my first solution.  And I would change the grade to A,  Submitting this comment as an objection.

Author Closing Comment

ID: 40222283
This solution was simpler and faster than my first, and ended up being ideal for my needs.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question