SQL Server Performance

Save Word, Excel, PDF and JPEG files in database

Discussion in 'Getting Started' started by madduri, Jun 6, 2007.

  1. madduri New Member

    Hi,

    I am trying to create a database in which I can save some Word, Excel, PDF & JPEG files. Could any one help me with some ideas or links which can explain the procedure how to save such files.

    Thanks in advance.

    Madduri
  2. satya.sqldba New Member

  3. madduri New Member

    Thank you Satya. I found this article but looking for some thing in detail....just in case....
  4. MohammedU New Member

    It is not advisable to store the files in the database... it is better to store the files on the file system and store the path and file in the database...

    check the following research paper...
    http://research.microsoft.com/research/pubs/view.aspx?msr_tr_id=MSR-TR-2006-45


    MohammedU.
    Moderator
    SQL-Server-Performance.com

    All postings are provided “AS IS” with no warranties for accuracy.
  5. Madhivanan Moderator

  6. satya Moderator

    I would like to ask what is volume of these files and do you have anything in mind to archive this data?

    quote:Originally posted by madduri

    Hi,

    I am trying to create a database in which I can save some Word, Excel, PDF & JPEG files. Could any one help me with some ideas or links which can explain the procedure how to save such files.

    Thanks in advance.

    Madduri

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  7. madduri New Member

    The file sizes range from 20 - 700 KB. Most of them are wordpads, PDfs and jpg formats. There are many documents and the number will keep increasing. We just want to save the documents in the database for security reasons and to limit the access.

    I have discussed about the issues and concerns about having these documents in the database. We just want to create a prototype with few documents added just to measure the effects.

    I have created a table named Files with the following columns

    ID int
    filetype varchar (20)
    File varchanr (max)

    Now i would like to insert the documents located on the harddrive in to the table. I want the document to go into the File column. I think we can do it using bulk insert but havent done yet. I am working on sql server 2005.

    Could you please give me a sample code to insert the document in to this table the way I want.

    Even any other simple way of doing it is fine.

    Your help is highly appreciated.

    Thanks in advance.

    Madduri
  8. satya Moderator

    You can make use of VARBINARY in SQL 2005
    http://aspalliance.com/das/insertimage.aspx
    http://sqlservergems.blogspot.com/2006/03/how-to-insert-image-into-sql-server.html andhttp://sqlservergems.blogspot.com/2006/02/using-max-in-varchar-varbinary-and.html

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  9. madduri New Member

    Thank you all for your useful links. It really helped. I used the following code to insert a word document, a jpg and a pdf.

    Insert into files (id, Filetype, [File])
    Select 3, 'My Pdf',
    BulkColumn from
    Openrowset( Bulk 'c:Test.pdf', Single_Blob) as [File]

    when i used the select statement, i find the data in the file column as encrypted. How do I retrieve the data through sql server. Will I only be able to pull the files and pictures through the application???

    Thank u all.

    Madduri
  10. satya Moderator

    Is it encrypted or returns any hexadecimal values?
    You can use READTEXT statement to return those columns using TSQL, refer to books online for more information.

    Satya SKJ
    Microsoft SQL Server MVP
    Writer, Contributing Editor & Moderator
    http://www.SQL-Server-Performance.Com
    This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.
  11. madduri New Member

    It looks like this:

    0xD0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF0900060000000000000000000000010000007300000000000000001000007500000001000000FEFFFFFF0000000072000000FFFFFFFF............



    Thanks, Madduri
  12. satya Moderator

  13. Madhivanan Moderator

  14. faiga16 New Member

    quote:Originally posted by madduri

    Thank you all for your useful links. It really helped. I used the following code to insert a word document, a jpg and a pdf.

    Insert into files (id, Filetype, [File])
    Select 3, 'My Pdf',
    BulkColumn from
    Openrowset( Bulk 'c:Test.pdf', Single_Blob) as [File]

    when i used the select statement, i find the data in the file column as encrypted. How do I retrieve the data through sql server. Will I only be able to pull the files and pictures through the application???

    Thank u all.

    Madduri


    Hey Madduri,

    I've tried your code to insert file on column but i got this error:
    Cannot bulk load. The file "c: ext.txt" does not exist.

    I'm pretty sure the file do exist.
    did you make the column as varbinary?
  15. Madhivanan Moderator

    The file should exist in Server and not at the client system

    Madhivanan

    Failing to plan is Planning to fail
  16. satya Moderator

    Its not Madduri's code [<img src='/community/emoticons/emotion-1.gif' alt=':)' />], anyway as suggested you need to check whether file exists or not on the specified path where you are trying to import or export.<br /><br /><b>Satya SKJ</b><br />Microsoft SQL Server MVP<br />Writer, Contributing Editor & Moderator<br /<a target="_blank" href=http://www.SQL-Server-Performance.Com>http://www.SQL-Server-Performance.Com</a><br /><center><font color="teal"><font size="1">This posting is provided AS IS with no rights for the sake of <i>knowledge sharing. <hr noshade size="1">Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.</i></font id="size1"></font id="teal"></center>

Share This Page