Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Saving binay data to disk via TSQL??? Expand / Collapse
Author
Message
Posted Friday, October 19, 2007 8:37 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:17 AM
Points: 205, Visits: 218
Is there a way of saving binary data, retrieved from the database, to disk using just TSQL?
Thanks...
Post #412833
Posted Friday, October 19, 2007 8:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #412846
Posted Friday, October 19, 2007 9:06 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:17 AM
Points: 205, Visits: 218
Thanks for the info Andras.

Interesting article.
Post #412858
Posted Saturday, October 20, 2007 7:05 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #413131
Posted Sunday, October 21, 2007 12:13 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:17 AM
Points: 205, Visits: 218
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?
Post #413196
Posted Sunday, October 21, 2007 12:45 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #413203
Posted Monday, October 22, 2007 12:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 11, 2009 9:36 PM
Points: 1, Visits: 14
I have used BCP to archive binary data to disk. You need to specify a format file with no format for this to work.
Post #413278
Posted Monday, October 22, 2007 1:19 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, September 11, 2013 7:37 AM
Points: 163, Visits: 287
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
Post #413286
Posted Monday, October 22, 2007 1:46 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:17 AM
Points: 205, Visits: 218
Thanks Martin & Nebjosa,

Looks exactly what I need. I will try it.

Ta

Post #413296
Posted Monday, October 22, 2007 6:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, September 5, 2014 7:17 AM
Points: 205, Visits: 218
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?
Post #413410
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse