Saving binay data to disk via TSQL???

Question

Saving binay data to disk via TSQL???

flurk118

SSC Eights!

Points: 820
More actions
October 19, 2007 at 8:37 am

#375991

Is there a way of saving binary data, retrieved from the database, to disk using just TSQL?
Thanks...

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

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

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

Writing binary data, especially if you want to write out to several files, several objects, ... to disk is a bit of a pain. Your best bet would be to write a small application say in C# :).

If that is not an option, have a look at http://www.sqlservercentral.com/articles/Miscellaneous/writingtoafileusingthesp_oacreatestoredprocedurean/1694/

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

flurk118 SSC Eights! Points: 820 More actions · Answer 2

flurk118

SSC Eights!

Points: 820

October 19, 2007 at 9:06 am

#743669

Thanks for the info Andras.

Interesting article.

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

Andras Belokosztolszki (10/19/2007)
Writing binary data, especially if you want to write out to several files, several objects, ... to disk is a bit of a pain. Your best bet would be to write a small application say in C# :).
If that is not an option, have a look at http://www.sqlservercentral.com/articles/Miscellaneous/writingtoafileusingthesp_oacreatestoredprocedurean/1694/
Regards,
Andras

Why aren't you considering BCP with no terminator?

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

flurk118 SSC Eights! Points: 820 More actions · Answer 4

I have a table with many rows, each containing an binary field containing, basically, an image. I would like to query this table and produce a file on disk for each of the rows.

Is this achievable using BCP?

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

Heh... I actually haven't tried it because I won't allow folks to store images in our databases... but I don't see why it wouldn't work... it's just another form of data.

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

Martin Reyerink-362775 SSC Rookie Points: 35 More actions · Answer 6

I have used BCP to archive binary data to disk. You need to specify a format file with no format for this to work.

Nebojsa Ilic SSCommitted Points: 1735 More actions · Answer 7

Hi, I'm using bcp to queryout images from database and T-SQL code looks something like this:

DECLARE c1 CURSOR FOR

SELECT (KeyField)

FROM (Table)

WHERE ...

OPEN c1

FETCH NEXT FROM c1

INTO @KeyField

WHILE @@FETCH_STATUS = 0

BEGIN

-- Create unique name for the file using KeyField from the table

SELECT @FileName = '(Server path ending with \)' + convert(varchar(18), @KeyField) + '.(ext)'

SET @bcpCommand = 'bcp "SELECT (image) FROM (Table) WHERE KeyField = ' + convert(varchar(18), @KeyField) + '" queryout "'

SET @bcpCommand = @bcpCommand + @FileName + '" -S (ServerName) -T -n'

EXEC master..xp_cmdshell @bcpCommand, no_output

FETCH NEXT FROM c1

INTO @KeyField

END

CLOSE c1

DEALLOCATE c1

flurk118 SSC Eights! Points: 820 More actions · Answer 8

Thanks Martin & Nebjosa,

Looks exactly what I need. I will try it.

Ta

flurk118 SSC Eights! Points: 820 More actions · Answer 9

I have tried the above but have run into an issue.

The table holds an image field containing either tiff, jpg or pdf.

The code works for pdf but the file created for tiff and jpg do not.

Is there a simple reason for this?

Nebojsa Ilic SSCommitted Points: 1735 More actions · Answer 10

To be honest to you I'm using this script for pdf files and I haven't test it for jpg. I'll do some test tomorrow if it's not too late to you.

Mauve SSChampion Points: 11316 More actions · Answer 11

The BCP method with a cursor and [server] command shell appears to be quite kludgy. First off, the extraction of the BLOB is written to the SQL Server server when you may wish to extract from a client machine. Never mind the security issues with invoking the command shell inside the server in a production environment. Cursors are not a good thing, and lastly, the constant context switching to the command shell to extract each record is a performance issue.

So an application is the proper choice. A quick program solution is to use VBScript as per the attached sample (just rename the extension from "txt" to "vbs").

[font="Arial Narrow"](PHB) I think we should build an SQL database. (Dilbert) What color do you want that database? (PHB) I think mauve has the most RAM.[/font]

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

Now you know why I don't let images and other blobs into may databases... too much of a hassle. I just store the names of files in the database and let the GUI guys pluck the images.

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

VaiydeyanathanVS Hall of Fame Points: 3053 More actions · Answer 13

try out this link with following samples.

http://support.microsoft.com/kb/258038

Nebojsa Ilic SSCommitted Points: 1735 More actions · Answer 14

In general I will agree with John and Jeff. But sometimes it's not just like that.

For example, having images on file system is good solution but then backup becomes an open issue. Security too. Who will do it, where, how you will do restore,...

... or ... you should avoid using cursor and bcp moving extract process to the client. It could be good approach but how will you automate this procedure in reliable way.

There are many questions you'd answer before you choose the solution.