Solved

Export CRM annotation attachments as files using classic VB

Posted on 2014-07-18
8
1,413 Views
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:

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!
0
Comment
Question by:jaw0807
  • 6
8 Comments
 
LVL 19

Expert Comment

by:Rikin Shah
ID: 40206109
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
 
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:
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
 

Author Comment

by:jaw0807
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.
0
 

Author Comment

by:jaw0807
ID: 40206653
Never mind. I downloaded Motobit's ScriptUtils and was able to do exactly what I needed using the ByteArray class.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:jaw0807
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.
0
 

Accepted Solution

by:
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:

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
 

Author Comment

by:jaw0807
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.
0
 

Author Closing Comment

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

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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.
Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now