Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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.
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

564 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