SQL Server and Word documents ....

[SteveBrett]

Hi,<br><br>Does anyone know how to add Word documents as objects in an SQL Server 7 database ?<br><br>I need to ass the docs, index them and then make them let users search their contents via a web interface ...<br><br>Tools available are Win 2000 server, Visual Studio 6 and any web authoring tools ...<br><br>many thnaks <br><br>Steve Brett

 

[calahans]

What you need is blob code (Binary Large Objects). You need to create a column of type IMAGE and save chunks of the object in binary. <br><br>&nbsp;&nbsp;&nbsp;&nbsp;Dim SQL As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim area&nbsp;&nbsp;As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim i As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim DataFile As Integer, Fl As Long, Chunks As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim Fragment As Integer, Chunk() As Byte<br>&nbsp;&nbsp;&nbsp;&nbsp;Const ChunkSize As Integer = 16384<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim filename As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim sSql As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim RS As rdoResultset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim filen As String<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim ires As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;' On Error GoTo Handler<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Screen.MousePointer = 11<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If txtProblem = &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ires = MsgBox(&quot;Fill in the problem text box please&quot;, vbCritical, &quot;Knowledge Base&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GoTo nofill<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If txtInfo = &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ires = MsgBox(&quot;Fill in the resolution text box please&quot;, vbCritical, &quot;Knowledge Base&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GoTo nofill<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If txtKWord = &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ires = MsgBox(&quot;Fill in the keyword text box please&quot;, vbCritical, &quot;Knowledge Base&quot<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;GoTo nofill<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;filen = txtDir<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If optVB = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;area = &quot;VB&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If optGen = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;area = &quot;General&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If optUNIX = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;area = &quot;UNIX&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If optInf = True Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;area = &quot;CORBA&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;area = &quot;SQL&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br><br>&nbsp;&nbsp;&nbsp;&nbsp;sSql = &quot;Select * from issues &quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set RS = cn.OpenResultset(sSql, rdOpenDynamic, rdConcurLock)<br><br>&nbsp;&nbsp;&nbsp;&nbsp;DataFile = 1<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If status = display Then GoTo update<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;RS.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;If txtDir &lt;&gt; &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Const conChunkSize = 32768<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Open filen For Binary Access Read As DataFile<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Fl = LOF(DataFile)&nbsp;&nbsp;&nbsp;&nbsp;' Length of data in file<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If Fl = 0 Then Close DataFile: Exit Sub<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Chunks = Fl \ conChunkSize<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Fragment = Fl Mod conChunkSize<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RS!doc.AppendChunk Null<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ReDim Chunk(Fragment)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Get DataFile, , Chunk()<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RS!doc.AppendChunk Chunk()<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;For i = 1 To Chunks<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ReDim Chunk(conChunkSize)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Get DataFile, , Chunk()<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;RS!doc.AppendChunk Chunk()<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Next i<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;RS!info = txtInfo<br>&nbsp;&nbsp;&nbsp;&nbsp;RS!keyword = txtKWord<br>&nbsp;&nbsp;&nbsp;&nbsp;RS!area = area<br>&nbsp;&nbsp;&nbsp;&nbsp;RS!prob = txtProblem<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;RS.update<br>&nbsp;&nbsp;&nbsp;&nbsp;RS.Close<br>&nbsp;&nbsp;&nbsp;&nbsp;clearall<br>&nbsp;&nbsp;&nbsp;&nbsp;If txtDir &lt;&gt; &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Close DataFile<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Screen.MousePointer = 1<br>&nbsp;&nbsp;&nbsp;&nbsp;frmADD.Hide<br>&nbsp;&nbsp;&nbsp;&nbsp;frmKB.Show<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>'Handler:<br>&nbsp;'&nbsp;&nbsp;&nbsp;Screen.MousePointer = 1<br>&nbsp;&nbsp;'&nbsp;&nbsp;i = MsgBox(&quot;Error Occured, &quot; & Err.Description, vbCritical, &quot;Knowledge Base&quot<br>&nbsp;&nbsp;'&nbsp;&nbsp;Exit Sub<br><br>nofill:<br>&nbsp;&nbsp;&nbsp;&nbsp;Screen.MousePointer = 1<br>&nbsp;&nbsp;&nbsp;&nbsp;Exit Sub<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>update:<br>&nbsp;&nbsp;&nbsp;&nbsp;sSql = &quot;update issues set info = '&quot; & txtInfo<br>&nbsp;&nbsp;&nbsp;&nbsp;sSql = sSql & &quot;', keyword='&quot; & txtKey<br>&nbsp;&nbsp;&nbsp;&nbsp;sSql = sSql & &quot;', prob = '&quot; & txtProblem & &quot;'&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;sSql = sSql & &quot;where issueid = &quot; & RS!issueid<br>&nbsp;&nbsp;&nbsp;&nbsp;cn.Execute sSql, rdExecDirect<br>&nbsp;&nbsp;&nbsp;&nbsp;GoTo nofill<br><br><br>Have not reviewed this code in ages - could be a bit rough but principle is there. <p>Cal<br><a href=mailto: > </a><br><a href= > </a><br>

 

[SteveBrett]

Many thanks for the swift reply, my manager has just informed me that his assistant has given me the worng details surrounding the project - he's looking for dynamically created word dox, with details stored on sql server, all modified and created via java scripts ....<br><br><br>... now where did i put that java book ....