SQL Server Performance Forum – Threads Archive

Save Word, Excel, PDF and JPEG files in database

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

Check if this can be of any help http://www.codeproject.com/cs/database/FileStore2DataBase.asp Satya

Thank you Satya. I found this article but looking for some thing in detail….just in case….

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.

http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx
http://support.microsoft.com/default.aspx?scid=kb;en-us;317016 Madhivanan Failing to plan is Planning to fail

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.

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

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.

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

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.

It looks like this: 0xD0CF11E0A1B11AE1000000000000000000000000000000003E000300FEFF0900060000000000000000000000010000007300000000000000001000007500000001000000FEFFFFFF0000000072000000FFFFFFFF………… Thanks, Madduri

Its hexadecimal value,http://www.dbazine.com/sql/sql-articles/larsen13 fyi. 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.

Also refer
http://www.nigelrivett.net/SQLTsql/ReplaceText.html Madhivanan Failing to plan is Planning to fail

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?

The file should exist in Server and not at the client system Madhivanan Failing to plan is Planning to fail

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. Satya SKJ Microsoft SQL Server MVP Writer, Contributing Editor & Moderator http://www.SQL-Server-Performance.Com</a> <center>This posting is provided AS IS with no rights for the sake of 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.</center>

]]>