Store word doc/image on sql server

Question

Store word doc/image on sql server

Waseem Jaleel

SSCommitted

Points: 1706
More actions
August 10, 2008 at 7:10 am

#382211

I am trying to store word docs/image on sql server using BLOB. Is there anyway we can implement this. Please advice.

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 1

Use the varbinary(max) data type. Also, look at SQO Server 2008 and filestream objects, which will work a lot better when storing this kind of data.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 2

Thank You for your response. Could you please give me more inputs on how I can implement this on SQL 2005.

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 3

I have absolutely no idea on what additional information you need. How you implement storing image data in SQL Server all depends upon what you need to do.

For example, if you are going to be storing a lot of documents (thousands) - you probably wouldn't want to store them in the database. You probably would want to store them in the file system and only store a link to the document.

However, if you can implement this on SQL Server 2008 - you could setup the filestream partition for your database and define the column with the filestream attribute and SQL Server will take care of storing the data in the file system.

For further information on filestream you can lookup the topic on Books Online.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I believe the question is how to load the Word file into the VARCHAR(MAX) element.

And, yes, I agree... it's probably better to store the name of the file in a table instead of the file itself. Still, how would you load the document into a VARCHAR(MAX) element?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeffrey Williams SSC Guru Points: 89773 More actions · Answer 5

Jeff Moden (8/10/2008)
I believe the question is how to load the Word file into the VARCHAR(MAX) element.
And, yes, I agree... it's probably better to store the name of the file in a table instead of the file itself. Still, how would you load the document into a VARCHAR(MAX) element?

Not sure what you are asking here. You use an INSERT statement and insert the data into the table, nothing special.

Now, if you are asking about using FILESTREAM - that I do not know yet. I have not had the opportunity to work with it yet, so I am not sure if there is anything you need to do other than define the varbinary column with the filestream attribute (after creating the filestream partition, of course).

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

No... the question is simple... you have a Word doc sitting in a directory somewhere... how do you load that file into a VARCHAR(Max) column (only one row, of course) in a database table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 7

This is not a task I need to do, it is for learning purpose. I happened to read that word docs/images can be stored in sql server using BLOB. I was trying to implement it, but was not exactly sure how... So it would be really enough that i get a method by which I can store atelast 1 word doc/ image 🙂

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

Ah ha! I knew I had an example squirreled away somewhere. Of course, you can do it with a GUI using ADO and all that... but here's a way to do it directly from a file...

CREATE TABLE dbo.SomeTable (ID INT IDENTITY(1,1), MyDoc VARBINARY(MAX))

INSERT INTO dbo.SomeTable(MyDoc)

SELECT * FROM OPENROWSET(BULK N'd:\somepath\somefilename.doc', SINGLE_BLOB) AS MyDoc

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Waseem Jaleel SSCommitted Points: 1706 More actions · Answer 9

Waseem Jaleel

SSCommitted

Points: 1706

August 10, 2008 at 10:38 pm

#855872

Just what I needed. Thanks a lot for Jeff... 🙂

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 10

...and thanks for the feedback! 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

sqlizer SSCrazy Points: 2090 More actions · Answer 11

Jeff,

Here comes another question. I just did what you have mentioned in the Script for storing file. It worked fine. I inserted a doc file in database. Now, how do you retrieve that file? when you run select statement like

SELECT * FROM dbo.SomeTable

it gives you an Hexadecimal info for the MyDoc column of the table. So how do you get original file back?

Chintan

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

THAT, my friend, it why I recommend only storing the file name and not the file itself. 😛

You either have to capture the info in a variable in a GUI and then open Word and pass that whole mess to it (I believe... I'm no pro on the GUI side) or you have to output that mess to a file again. I've never tried that because I always save just the file name in the table. 😀 I believe you can do it by creating an empty file (perhaps using xp_CmdShell) and then inserting into the text file using OPENROWSET again but places reversed.

Sorry I don't know for sure because I just don't do it this way... in fact, I don't allow my developers to store files in the database at all.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

virgo Ten Centuries Points: 1266 More actions · Answer 13

Hi jeff,

That was informative...is ther a way using sql query to retrive the doc,image,pdf from the sql server tables to local computer with out using any ADO or front end tools.

Thanks in advance

virgo Ten Centuries Points: 1266 More actions · Answer 14

Hi Jeff,

can u please let me know similar query to retrive doc, image,pdf files stored in sql server tables.

Thanks in advance