Como exportar uma coluna de imagem para arquivos no SQL Server?

Esta é a solução que encontrei:

1. Ativar xp_cmdshellcom

   EXEC sp_configure 'show advanced options', 1
   GO
   RECONFIGURE
   GO
   EXEC sp_configure 'xp_cmdshell', 1
   GO
   RECONFIGURE
   GO
   

2. Se necessário, crie um diretório xp_cmdshellpara obter as permissões necessárias.

   EXEC master..xp_cmdshell 'mkdir C:\exportdir'
   

3. Usar BCP com consulta

   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 deve ser o nome da tabela totalmente qualificado, ou seja, [Yourdb].[YourSchema].[YourTable]

Eu tive o mesmo problema com os 4 bytes extras sendo adicionados ao início de todos os meus arquivos também. Em vez de usar a opção -N no meu comando bcp, alterei para -C RAW. Ao fazer isso, o bcp será solicitado com as seguintes perguntas:

Digite o tipo de armazenamento de arquivo do campo FileData [imagem]:
Insira o comprimento do prefixo do campo FileData [4]:
Insira o comprimento do campo FileData [0]:
Digite o terminador de campo [nenhum]:
Deseja salvar essas informações de formato em um arquivo? [S/n]

Para corrigir isso, criei um arquivo de texto (i.txt) na raiz do meu servidor sql que continha as seguintes linhas para responder a cada uma delas:

EU
0
0

n

Então, minha linha EXEC bcp se tornou:

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'

Isso exportou meu arquivo sem nenhum dos caracteres extras.

Vim por aí procurando uma solução para exportar uma coluna IMAGE tendo armazenado diferentes tipos de arquivos (pdf, xls, doc, xml ...) que quero exportar.

A abordagem na resposta funcionou apenas para arquivos pdf. Para exportar todos os tipos de arquivos tive que ajustar a solução da seguinte forma:

(1.) Crie um arquivo de modelo de formato:

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.) Abra o arquivo de formato de exportação criado e edite-o desta forma:

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

Em seguida, execute seu comando de exportação:

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.) Caso você encontre esse erro (como eu fiz):

"[Microsoft][SQL Native Client]As colunas do arquivo host podem ser ignoradas somente ao copiar para o servidor"

certifique-se do seguinte:

• certifique-se de não esquecer de editar a segunda linha e inserir o número de entradas aqui (1 neste cenário)!
• certifique-se de ter um CRLF no final da última linha, sem isso não funcionou!

Depois disso, a exportação de qualquer palavra do arquivo IMAGE Column sem erro.

Homenagens para 1. e 2. tudo vai para a resposta da seguinte pergunta: https://stackoverflow.com/questions/1366544/how-to-export-image-field-to-file/24006947#24006947

Se você não tiver nenhum problema com uma solução de GUI, existe um ótimo complemento para SSMS SSMSBoost que fornece muitos recursos úteis e, claro, a maneira mais simples de visualizar imagens armazenadas em SQL (pelo menos na minha opinião)

NOTA : Você deve reiniciar o SSMS após instalar este suplemento.

Instale-o e aproveite a visualização de imagens apenas com: RightClick> Visualize As> Picture

Antes de continuar, certifique-se de que o comando xp_cmdshell esteja habilitado, pois está desabilitado por padrão.

EXEC sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE
GO
EXEC master..xp_cmdshell 'mkdir D:\Project\Member\Images'

Mantenha o comando em UMA LINHA - LINHA ÚNICA!!!

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