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?
This is the solution I came up with:
EXEC sp_configure 'show advanced options', 1 GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 GO RECONFIGURE GO
If needed create a directory with
xp_cmdshellto get the needed permissions.
EXEC master..xp_cmdshell 'mkdir C:\exportdir'
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]
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 : Enter length of field FileData : 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.
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
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
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
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