Documents

Unfortunately, because T-SQL doesn't support writing its Binary Large Object data directly to files, you must write an application (in a .Net language, or Visual Basic with ActiveX Data Objects, for example) to do so. There are several articles in Microsoft's Knowledge Base that include code samples.

There's one other complication, however, that's a bit more difficult to work around. The documents stored in t_cust_doc are stored as "OLE objects"; this is what enables them to be opened, from Tessitura, in their native applications. As a result, an "OLE header" is added to the stream of data stored in the doc_contents column. Long story short: this header would have to be identified and removed to finally end up with a true ".JPG" or ".DOC" file (or whatever binary file format is stored).

Hope this helps a bit, and isn't too discouraging! It can be done, but it's certainly not a trivial undertaking.

Don't suppose anyone out there in the community has written such an application they're proud enough of to share?  I'd be happy to tell everyone how awesome you are if so...

In all seriousness, any help would be appreciated.  Thanks.

Hi, in an ASP.Net page you could fdo something like below.  This saple does not check for the proper mime types it is just assuming a gif file.   There are a lot of topis on the web about this as well.  The main part is where it strips out the ole header data.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

public

 

 

partial class test : System.Web.UI.

Page

{

 

 

protected void Page_Load(object sender, EventArgs

e)

{

 

 

SqlConnection

m_sqlcnDBC;

 

 

SqlCommand

m_sqlcmdObj;

 

 

m_sqlcnDBC =

 

new SqlConnection("Data Source=test;Initial Catalog=impresario;User ID=;Password=;max pool size=200"

);

m_sqlcnDBC.Open();

m_sqlcmdObj =

 

new SqlCommand

();

m_sqlcmdObj.Connection = m_sqlcnDBC;

 

 

 

DataSet ds = new DataSet("RESULTS"

);

 

 

SqlDataAdapter sqladpResult = new SqlDataAdapter("select doc_contents,doc_title from t_cust_doc where customer_no=2187966"

, m_sqlcnDBC);

sqladpResult.Fill(ds);

 

 

 

 

 

const int

OleHeaderLength = 78;

 

 

foreach (DataRow datarow in

ds.Tables[0].Rows)

{

 

 

int strippedDataLength = ((byte[])datarow["doc_contents"

]).Length - OleHeaderLength;

 

 

byte[] strippedData = new byte

[strippedDataLength];

 

 

Array.Copy((byte[])datarow["doc_contents"

], OleHeaderLength,

strippedData, 0, strippedDataLength);

Response.ContentType =

 

"image/gif"

;

 

Response.BinaryWrite(strippedData);

 

}

}

}

Hey, Matt.  I was just looking into this same question... don't suppose you've solved this in the last few months and would be willing to share?

Thanks!

Emily Levada