Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    1

    Question Unanswered: Store Ms-word document files into sql database

    Is it possible to store a ms-word document of 10 or more pages in a sql server 2000 database , so that it can be seen from different servers accessing the same file, across the network.

    The file which will be stored in the database will later be editted , modified dynamically over the network by different people.

    if its not possible to do such a thing using sql server , then can anyone suggest a more robust solution to handle this.

    thanks in advance
    rachna

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Store Ms-word document files into sql database

    Originally posted by real_rachna
    Is it possible to store a ms-word document of 10 or more pages in a sql server 2000 database , so that it can be seen from different servers accessing the same file, across the network.

    The file which will be stored in the database will later be editted , modified dynamically over the network by different people.

    if its not possible to do such a thing using sql server , then can anyone suggest a more robust solution to handle this.

    thanks in advance
    rachna
    Look up image and text datatypes...but you would be far better served to store a file path and name in your database.

  3. #3
    Join Date
    Oct 2003
    Location
    Tijeras, NM
    Posts
    12
    This procedure works from VB/ADO/OLEDB, and it's easy.

    1. Load the entire document into a byte array.
    2. Assign the byte array to directly to a streamed field:

    RS!ImageField=MyByteArray

    3. It's possible that when the byte array exceeds a certain size, it will be truncated. I haven't reached that limit, but I fear that I may in time--the limit used to be 8K until Sql Server 7, I believe. If that happens, just use multiple .AppendChunks with smaller byte arrays. Let me know if you get stuck, I do this all the time.
    I love animals. They're delicious.

  4. #4
    Join Date
    Oct 2003
    Location
    Pune, India
    Posts
    14

    Re: Store Ms-word document files into sql database

    Yes, it is possible to store a MS Word document in SQL Server database. You need to have the column data type as image.

    But here is a limit - THE STORED DOCUMENT CANNOT BE GREATER THAN 16MB IN SIZE.

    If the file size is your concern please do not opt for storing the Document in the database. Instead upload in the server as a physical file. Otherwise you can go ahead storing the document as a image type (BLOB) using ADO Stream object.

    A sample code is given below. (In the sample code, the server name is \\DIPAYAN. Please change the server name, DB name, userID & Password according to your need) - Also you can try the attached zip file which is a small VB project.

    ********** SAMPLE CODE STARTS HERE ****************
    Dim cn As ADODB.Connection 'Opening a Connection for BLOB
    Dim rs As ADODB.Recordset
    Dim mstreamBLOB As ADODB.Stream


    'Save the SQL Data to a Windows File
    Private Sub cmdFetchData_Click()
    Dim startLoad As Date
    Dim endLoad As Date
    startLoad = Now
    MsgBox startLoad
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;data Source=DIPAYAN;Initial Catalog=pubs;User Id=sa;Password=''"
    Set rs = New ADODB.Recordset
    rs.Open "Select * from pub_info where pub_id='1622'", cn, adOpenKeyset, adLockOptimistic

    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.Write rs.Fields("logo").Value
    mstream.SaveToFile "F:\Dipayan\BLOBRND\RetrievedNewTextDocument.t xt", adSaveCreateOverWrite

    rs.Close
    cn.Close
    endLoad = Now
    MsgBox endLoad
    End Sub

    'Save the Windows File to SQL Image Column
    Private Sub cmdStoreData_Click()
    Dim startLoad As Date
    Dim endLoad As Date
    startLoad = Now
    MsgBox startLoad
    Set cn = New ADODB.Connection
    cn.Open "Provider=SQLOLEDB;data Source=DIPAYAN;Initial Catalog=pubs;User Id=sa;Password=''"

    Set rs = New ADODB.Recordset
    rs.Open "Select * from pub_info where pub_id='1622'", cn, adOpenKeyset, adLockOptimistic

    Set mstream = New ADODB.Stream
    mstream.Type = adTypeBinary
    mstream.Open
    mstream.LoadFromFile "F:\Dipayan\BLOBRND\NewTextDocument.txt"
    rs.Fields("logo").Value = mstream.Read
    rs.Update

    rs.Close
    cn.Close
    endLoad = Now
    MsgBox endLoad
    End Sub

    ********** SAMPLE CODE ENDS HERE ****************

    Thanks & Regards
    Dipayan Sarkar
    (dipayan@covisible.com)
    [CoVisible Solutions - a Document Management Software company)
    Attached Files Attached Files

  5. #5
    Join Date
    Oct 2003
    Location
    Tijeras, NM
    Posts
    12
    16 MB? That' bites. It's nice to know you can convert a database to a file management systeem when you need to. . Thanks for the info. I believe you saved me some trouble in the future.
    I love animals. They're delicious.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •