Store word doc/image on sql server

Question

Store word doc/image on sql server

Viewing 7 posts - 16 through 21 (of 21 total)

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

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 1

virgo (8/11/2008)
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

Sure you can retrieve it using a query... but it won't look any better than if you opened the original file in the Binary mode of Text Pad. Without a front end that correctly interprets the binary, it's just going to look like Hexadecimal gibberish.

Again, I'm no GUI expert... but I believe there are "objects" in a lot of the front end development tools that will display it correctly. But I'm just guessing here. Even web sites that store PDF's download a file to your machine to display it. If you don't have Adobe PDF Reader (or similar), you won't be able to read the file with human eyes.

--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)

virgo Ten Centuries Points: 1266 More actions · Answer 2

can u give the query i can use for retrieving the files stored in sql table..so that i can download it to local system and then i can format it.

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 3

Like I said, I've never done it before and your guess is as good as mine... maybe something like...

--===== Write text line to file

CREATE PROCEDURE sp_AppendToFile(@FileName varchar(255), @Text1 varbinary(MAX)) AS

DECLARE @FS int, @OLEResult int, @FileID int

EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT

IF @OLEResult <> 0 PRINT 'Scripting.FileSystemObject'

--Open a file

execute @OLEResult = sp_OAMethod @FS, 'OpenTextFile', @FileID OUT, @FileName, 8, 1

IF @OLEResult <> 0 PRINT 'OpenTextFile'

--Write Text1

execute @OLEResult = sp_OAMethod @FileID, 'WriteLine', Null, @Text1

IF @OLEResult <> 0 PRINT 'WriteLine'

EXECUTE @OLEResult = sp_OADestroy @FileID

EXECUTE @OLEResult = sp_OADestroy @FS

Like I said... just a guess... @Text1 would be the Word Doc you had saved in a row...

--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)

BobAtDBS SSCarpal Tunnel Points: 4590 More actions · Answer 4

Seldom is the answer "never do it this way". It often "depends".

We host a "stock exchange" server for our customers. They trade a lot of documents and data between each other. We built a system which includes storing blobs in the sql database and it's worked wonderfully for several years (starting in sql 7, now 2005). Since we wrote the software for the customers we control "both ends and the middle" and we decided to keep everything in one application and not have to mess with using FTP or something else to move the files. Keeping the blobs in the database also simplifies our backups and warm standby servers.

We setup a separate file group for the blobs to live in. Just a normal server in terms of resources (16gig ram, dual cpu, multiple raid sets for logs, data, temp). The clients use ADO in a VB application to stuff the docs into recordsets and store them on the server.

Every time I read someone saying "never store blobs in the server" I wonder how many times they've actually implemented it.

Student of SQL and Golf, Master of Neither

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 5

Jeffrey Williams (8/10/2008)
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.

Volume of data is the least important consideration when deciding to store files in the database or on a file server. In fact, SQL Server will store large numbers of files more efficiently, more safely, and easier to backup and recover than using a file server.

The most important consideration is the security of the files and fileserver, and the risk of having disparate pieces of data (file and filename column in the database) out of sync. If the fileserver can be manipulated outside of the application system that will maintain the file name in hte database, that can lead to mismatches and bad data. If it is necessary to ensure files cannot be added or removed without the database being updated, it is much better and safer to store the file in the database, where only the application can access it.

Mark

Mark Harr SSCrazy Eights Points: 9921 More actions · Answer 6

BobAtDBS (8/12/2008)
Seldom is the answer "never do it this way". It often "depends". ...
Every time I read someone saying "never store blobs in the server" I wonder how many times they've actually implemented it.

Amen, Bob. Too many "experts" only know their own fishbowl.

Mark

Jeff Moden SSC Guru Points: 1004409 More actions · Answer 7

BobAtDBS (8/12/2008)
Seldom is the answer "never do it this way". It often "depends".
We host a "stock exchange" server for our customers. They trade a lot of documents and data between each other. We built a system which includes storing blobs in the sql database and it's worked wonderfully for several years (starting in sql 7, now 2005). Since we wrote the software for the customers we control "both ends and the middle" and we decided to keep everything in one application and not have to mess with using FTP or something else to move the files. Keeping the blobs in the database also simplifies our backups and warm standby servers.
We setup a separate file group for the blobs to live in. Just a normal server in terms of resources (16gig ram, dual cpu, multiple raid sets for logs, data, temp). The clients use ADO in a VB application to stuff the docs into recordsets and store them on the server.
Every time I read someone saying "never store blobs in the server" I wonder how many times they've actually implemented it.

There are some things in SQL Server that do deserve a "never"... kinda like you should never clean a loaded gun with the muzzle in your mouth. 😉

--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)