Solved

Save Image to Disk in T-SQL

Posted on 2011-02-18
4
1,280 Views
Last Modified: 2012-05-11
Hello Experts,

We need to extract a BLOB from our SQL Server database, and then write that image to disc. The image is a Microsoft Word doc. We think we're almost there but are stuck on one last thing.

When the file is actually written to disk, using the ADODB.stream object, only a small fraction of the file is written to disk (about 1 KB). The file/image is verified to be in the column/field in its entirety,  as we can successfully extract it using our client via ADO.GetChunk methods.

Any ideas as to the cause?

Thanks,
Bleary-eyed
DECLARE @mytextptr varbinary(16), @totalsize int,
     @lastread int, @readsize int,@AutoID int,@ObjectToken INT

SET NOCOUNT ON
SET @AutoID =1103 --limit to the record of interest

SELECT
    @mytextptr=TEXTPTR(Attachments), @totalsize=DATALENGTH(Attachments),
     @lastread=0,
     @readsize=CASE WHEN (@@TEXTSIZE < DATALENGTH(Attachments)) THEN
         @@TEXTSIZE ELSE DATALENGTH(Attachments) END
      FROM tblInsertedObjects WHERE AutoID=@AutoID
 
-- loop through as needed to populate @mytextptr
IF @mytextptr IS NOT NULL AND @readsize > 0
     WHILE (@lastread < @totalsize)
     BEGIN
         READTEXT tblInsertedObjects.Attachments @mytextptr @lastread @readsize
         IF (@@error <> 0)
             BREAK    
         SELECT @lastread=@lastread + @readsize
         IF ((@readsize + @lastread) > @totalsize)
             SELECT @readsize=@totalsize - @lastread
     END

-- Save @mytextptr to disk
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @mytextptr
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'd:\This_is_the_output.doc',2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken

Open in new window

0
Comment
Question by:Bleary-Eyed
[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
4 Comments
 
LVL 15

Expert Comment

by:Aaron Shilo
ID: 34931826
0
 

Author Comment

by:Bleary-Eyed
ID: 34933080
Hi ashilo,

Thanks for your response. However, we need assiatance with this in T-SQL.
0
 

Accepted Solution

by:
Bleary-Eyed earned 0 total points
ID: 34938350
We figured it out. there was no need for the READTEXT. We just placed the BLOB field into the myTestPtr variable in the Select statement and then used the -- ---Save @mytextptr to disk-- code to save it to disk. Worked great.
0
 

Author Closing Comment

by:Bleary-Eyed
ID: 34978014
We figured it out on our own
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
I have a large data set and a SSIS package. How can I load this file in multi threading?
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to shrink a transaction log file down to a reasonable size.

717 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