Export CRM annotation attachments as files using classic VB

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:

application/octet-stream
application/pdf
application/vnd.ms-excel
application/vnd.openxmlformats-officedocument.wordprocessingml.document


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!
jaw0807Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rikin ShahMicrosoft Dynamics CRM ConsultantCommented:
Hi,

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?
0
Anthony PerkinsCommented:
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:
http://www.experts-exchange.com/Database/MS-SQL-Server/Q_28462618.html

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.
0
jaw0807Author Commented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

jaw0807Author Commented:
Never mind. I downloaded Motobit's ScriptUtils and was able to do exactly what I needed using the ByteArray class.
0
jaw0807Author Commented:
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.
0
jaw0807Author Commented:
Actually, there was a simpler and more efficient way.  

Found the basics on some built-in SQL functions for Base64 here:

http://blog.falafel.com/Blogs/adam-anderson/2012/10/18/t-sql-easy-base64-encoding-and-decoding
 
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 
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

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"))
	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 & "  Sizes(table/disk): " & _
		rS("filesize") & "/" & fsO.GetFile(fOut).Size & " FilePath: " & fOut 
    rS.MoveNext
Loop

rs.Close

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.
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jaw0807Author Commented:
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.
0
jaw0807Author Commented:
This solution was simpler and faster than my first, and ended up being ideal for my needs.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Dynamics

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.