Storing Microsoft Word 97 documents in a SQL Server column

Question

I have this database with the following table, but I have no way to decrypt it.

DATA, TYPE, FILE TYPE, SIZE, DOC TYPE 0x15234324 , Word.Document.8 ,DOC, 19968, WORD.DOCUMENT.8 

The field seems to contain a text document stored in the SQL Server IMAGE column

Has anyone come across this before or a way to extract this data in a readable format?

So far, I have tried using PHP to extract a file and write it to a text document, but have not had much luck.

UPDATE: Now I have Visual Studio Express and you want to extract this data and save it in a Word document

UPDATE2: This is what I have in VB sofar

 Imports System.Data.SqlClient Imports System.IO Public Class Form1 Private Shared Function RetrieveFile(ByVal filename As String) As Byte() Dim connection As New SqlConnection("Server=sqlsrv;database=database;Trusted_Connection=Yes;") Dim command As New SqlCommand("select data from objects where object_ref in (select data from parts where object_ref =239804)", connection) command.Parameters.AddWithValue("test", filename) connection.Open() Dim reader As SqlDataReader = command.ExecuteReader(System.Data.CommandBehavior.SequentialAccess) reader.Read() Dim memory As New MemoryStream() Dim startIndex As Long = 0 Const ChunkSize As Integer = 256 While True Dim buffer As Byte() = New Byte(ChunkSize - 1) {} Dim retrievedBytes As Long = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize) memory.Write(buffer, 0, CInt(retrievedBytes)) startIndex += retrievedBytes If retrievedBytes <> ChunkSize Then Exit While End If End While connection.Close() Dim data As Byte() = memory.ToArray() memory.Dispose() Return data End Function Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim saveFileDialog1 As New SaveFileDialog() saveFileDialog1.Filter = "Doc File|*.doc" saveFileDialog1.Title = "Save an doc File" saveFileDialog1.ShowDialog() If saveFileDialog1.FileName <> "" Then Dim fs As New System.IO.FileStream(saveFileDialog1.FileName, System.IO.FileMode.Create, System.IO.FileAccess.Write) Dim data As Byte() = RetrieveFile("test.doc") fs.Write(data, 0, data.Length) fs.Flush() fs.Close() End If End Sub End Class 

Answer 1

I wrote a VBS script to pull data from sharepoint quotes some time ago, here is the general version:

 Const adOpenKeyset = 1 Const adLockOptimistic = 3 Const adTypeBinary = 1 Const adSaveCreateOverWrite = 2 strSQLServer = "YOURSERVER" strSQLDatabase = "YOURDB" strRecordID = "123" strTempFileName = "c:\output.doc" Set objConn = CreateObject("ADODB.Connection") Set objRS = CreateObject("ADODB.RecordSet") Set objStream = CreateObject("ADODB.Stream") objConn.Open "Provider=SQLOLEDB;data Source=" & strSQLServer & ";Initial Catalog=" & strSQLDatabase & "; Trusted_Connection=yes;" objRS.Open "Select * from AllDocStreams WHERE ID='" & strRecordID & "'", objConn, adOpenKeyset, adLockOptimistic objStream.Type = adTypeBinary objStream.Open objStream.Write objRS.Fields("Content").Value objStream.SaveToFile strTempFileName, adSaveCreateOverWrite objRS.Close objConn.Close 

Answer 2

C # code:

 connection.Open(); SqlCommand command1 = new SqlCommand("select DATA from TABLE where ...", connection); byte[] img = (byte[])command1.ExecuteScalar(); File.WriteAllBytes("your_path/word.doc", img); 

It must be logic. Write something similar in any language that you know. This should not be difficult for PHP or what you use.

Answer 3

take a look at this topic.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754

The guy does this using SCRIPTING.FILESYSTEMOBJECT to extract the contents from the IMAGE column and write it to a file.

read the comments.

Answer 4

Try something like this, replace the "some" values ​​with your own:

 declare @doc varbinary(max), @ObjectToken int select @doc = (select data from yourTable were someID = @idThatYouWant) set @FileName = '\someFolder\' + 'someFilename.doc' print 'Processing: ' + isnull(@FileName, 'null') exec sp_oacreate 'ADODB.Stream', @objecttoken output exec sp_oasetproperty @objecttoken, 'type', 1 exec sp_oamethod @objecttoken, 'open' exec sp_oamethod @objecttoken, 'write', null, @doc exec sp_oamethod @objecttoken, 'savetofile', null, @FileName, 2 exec sp_oamethod @objecttoken, 'close' exec sp_oadestroy @objecttoken 

Answer 5

I think you will get an IndexOutOfRangeException in the following line of your code:

  Dim retrievedBytes As Long = reader.GetBytes(1, startIndex, buffer, 0, ChunkSize) 

because the index of the first parameter is 0 not 1

In any case, I suggest you use a method similar to that proposed by Clark, which is simpler and more readable.