Solved

T-SQL writing a blob to an external file

Posted on 2009-07-07
5
971 Views
Last Modified: 2012-05-07
Having just figured out how to use OPENROWSET(BULK '...', SINGLE_BLOB) to read binary data from the file system into my SQL Server database, I now need to do the opposite:

Given an "image" type column in a table, I need T-SQL script to write this to a file somewhere on the file system. Again, ideally as NATIVE as possible.

(dang - why are examples of this so hard to find?)

So imagine your select statement to get the blob is

SELECT [data] from [dbo].[blobtable] where [id] = 5

the [data] happens to represent a JPG, so what I need is a wee bit o' T-SQL code that will write [data] out of the above select to 'C:\test.jpg'

many thanks
0
Comment
Question by:WernerVonBraun
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 24792774
there are some methods:
http://www.sqlservercentral.com/articles/Miscellaneous/writingtoafileusingthesp_oacreatestoredprocedurean/1694/

note: the methods all write TEXT, however the ones with vbscript or sp_oaxxxx methods should be easily modifiable to write binary data.
0
 
LVL 4

Author Comment

by:WernerVonBraun
ID: 24792799
:-(

I'm not sure it would be easy to modify them to write binary data. The only way I can imagine that would work is by using an ADODB.Stream - however in that case I would at some stage need to store the data locally in a variable of a binary type, and SQL Server dinnae like that?
0
 
LVL 31

Accepted Solution

by:
RiteshShah earned 500 total points
ID: 24792879
look at example in below link, there is a way to store image file in table (in blog article) and retrieve it back to file system in SQL Server itself (in comment given by the reader of blog).


http://blog.sqlauthority.com/2009/03/06/sqlauthority-news-author-visit-toronto-canada-insert-image-in-database/

0
 
LVL 4

Author Comment

by:WernerVonBraun
ID: 24793061
damn. It involves invoking an external .exe but at least it's "native":

bcp "SELECT BULKCOLUMN from OpenRowSet(BULK 'somefile', SINGLE_BLOB) DMSData" queryout yetanotherfile -U someusername -P itspassword -n
0
 
LVL 4

Author Comment

by:WernerVonBraun
ID: 24793068
obviously instead of the OpenRowSet I used above you can select a blob from another table
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Using this function 4 52
Replication failure 1 31
Linked Server - SP with Param to VIew 7 29
SQL syntax question 6 69
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question