Export CRM annotation attachments as files using classic VB

Posted on 2014-07-18
Medium Priority
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 20

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.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

621 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