12

I will migrate from a database. There is one column of type image that I would like to export to binary files on the file system. One file for each record. How can I do this with SQL Server?

10

This is the solution I came up with:

  1. Enable xp_cmdshell with

    EXEC sp_configure 'show advanced options', 1
    GO
    RECONFIGURE
    GO
    EXEC sp_configure 'xp_cmdshell', 1
    GO
    RECONFIGURE
    GO
    
  2. If needed create a directory with xp_cmdshell to get the needed permissions.

    EXEC master..xp_cmdshell 'mkdir C:\exportdir'
    
  3. Use BCP with queryout

    EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -N'
    

**your_db must be the fully qualified table name, i.e [Yourdb].[YourSchema].[YourTable]

  • 1
    I tried the above code and it did indeed work ... to a point. For some reason the JPG image has 4 preceding characters that cause it to become invalid. I removed these with a text editor and voila ... JPG worked. Any idea what these 4 characters could be and how to get rid of them in the export? They are: 2B 90 01 00 – user91489 Apr 6 '16 at 20:50
  • What is the type of the column you exported from? – Max Vernon Apr 6 '16 at 21:02
10

I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp will be prompted with the following questions:

Enter the file storage type of field FileData [image]:
Enter prefix-length of field FileData [4]:
Enter length of field FileData [0]:
Enter field terminator [none]: 
Do you want to save this format information in a file? [Y/n] 

To fix this I created a text file (i.txt) on the root of my sql server which had the following lines within it to answer each of these:

I
0
0

n

Then, my EXEC bcp line became:

EXEC master..xp_cmdshell 'BCP "SELECT column_of_type_image FROM **your_db WHERE id = 1 " queryout "C:\exportdir\yourfile.pdf" -T -C RAW<C:\i.txt'

This exported my file without any of the extra characters.

2

I came around looking for a solution to export an IMAGE Column having stored different kind of file types (pdf, xls, doc, xml ...) I want to export.

The approach in the answer worked for pdf files only. To export all kinds of files I had to adjust the solution as follows:

(1.) Create a format template file:

Declare @sql varchar(500); 
Declare @sql varchar(500); 
SET @sql = 'bcp db.dbo.ImgTable format nul -T -n -f C:\tmp\export.fmt -S ' + @@SERVERNAME; 
select @sql;  
EXEC master.dbo.xp_CmdShell @sql; 

(2.) Open the created export format file and edit it that way:

10.0
1
1       SQLIMAGE            0       0       ""   1     img_col                                     ""

Then execute your export command:

EXEC master..xp_cmdshell 'BCP "SELECT IMG_COL FROM db.dbo.ImgTable WHERE id = ''CAB240C0-0068-4041-AA34-0000ECB42DDD'' " queryout "C:\tmp\myFile.xml" -T -f C:\tmp\export.fmt -S '

(3.) In case you run into this error (as I did):

"[Microsoft][SQL Native Client]Host-file columns may be skipped only when copying into the Server"

make sure the following:

  • make sure you didn't forget to edit the second row and enter the number of entries here (1 in this scenario)!
  • make sure you have a CRLF at the end of the last line, without this it did not work!

After this, the export of any IMAGE Column file word without an error.

Tributes for 1. and 2. all goes to the answer of the following question: https://stackoverflow.com/questions/1366544/how-to-export-image-field-to-file/24006947#24006947

1

If you have not any problem with a GUI solution, there is a really great add-in for SSMS SSMSBoost which provides plenty of useful features, and of course the simplest way to preview images stored in SQL(at least in my opinion)

NOTE : You must restart SSMS after installing this add-in.

Install it and enjoy previewing images just with : RightClick > Visualize As > Picture

0
EXEC master..xp_cmdshell 'mkdir D:\Project\Member\Images'

Keep the command on ONE LINE - SINGLE LINE!!!

SET @Command = 'bcp "SELECT Member_Picture FROM dbserver.[Member_Image] WHERE memberId = 1 " queryout "D:\Project\Member\Images\member1.jpg" -T -N ' 
PRINT @Command -- debugging
EXEC xp_cmdshell   @Command
GO
-2
USE [POC]
DECLARE @outPutPath varchar(50) = 'C:\Extract_Photos'
, @i bigint
, @init int
, @data varbinary(max)
, @fPath varchar(max)
, @folderPath  varchar(max)

--Get Data into temp Table variable so that we can iterate over it
DECLARE @Doctable TABLE (id int identity(1,1), [Doc_Num]  varchar(100) , [FileName]  varchar(100), [Doc_Content] varBinary(max) )

INSERT INTO @Doctable([Doc_Num] , [FileName],[Doc_Content])
Select [STUDENTNO] , [STUDENTNAME],[STUDENTPHOTO] FROM  [dbo].[STUDENTPHOTOS]

--SELECT * FROM @table

SELECT @i = COUNT(1) FROM @Doctable

WHILE @i >= 1
BEGIN

    SELECT
     @data = [Doc_Content],
     @fPath = @outPutPath + '\'+ [Doc_Num] + '\' +[FileName],
     @folderPath = @outPutPath + '\'+ [Doc_Num]
    FROM @Doctable WHERE id = @i

  --Create folder first
  EXEC  [dbo].[CreateFolder]  @folderPath

  EXEC sp_OACreate 'ADODB.Stream', @init OUTPUT; -- An instace created
  EXEC sp_OASetProperty @init, 'Type', 1;
  EXEC sp_OAMethod @init, 'Open'; -- Calling a method
  EXEC sp_OAMethod @init, 'Write', NULL, @data; -- Calling a method
  EXEC sp_OAMethod @init, 'SaveToFile', NULL, @fPath, 2; -- Calling a method
  EXEC sp_OAMethod @init, 'Close'; -- Calling a method
  EXEC sp_OADestroy @init; -- Closed the resources

  print 'Document Generated at - '+  @fPath

--Reset the variables for next use
SELECT @data = NULL
, @init = NULL
, @fPath = NULL
, @folderPath = NULL
SET @i -= 1
END
  • Please amend your post by explaining what this code is supposed to do. – dezso Sep 15 '17 at 14:29
  • Thanks Erax for the script it worked wonderfully. PS: The script fails to extract the file if ran remotely using SSMS. It had to be run on the SQLServer directly for it to pull the attachment. – Vaas Feb 27 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.