0

I'm trying to export images from SQL server using bcr, I am also wondering how to do it so I decided to try it by myself. So my question is how can I export images from mssql succesfully, and what I did so far is described here:

On the command line I created file formatfile-n.fmt and located it to E: disk, because I searched the web a lot and I saw that its needed to edit that file to set it's SQLBINARY value from 8 to 0. Read that at this post (we are talking about images, that's reason why we need to edit SQLBINARY).

I executed command (below) and I exported images to location I wanted to, all images are there, but they have no size!

Here is my full code when images has no size:

EXEC sp_configure 'show advanced options', 1 

GO  RECONFIGURE  GO  EXEC sp_configure 'xp_cmdshell', 1  GO  RECONFIGURE  GO

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

DECLARE @ID as int DECLARE @SQL as varchar(4000)

DECLARE  csr CURSOR FOR SELECT BrojIskaznice FROM [Savez].[dbo].[Igraci]

OPEN csr

FETCH NEXT FROM csr INTO @ID

WHILE @@FETCH_STATUS = 0  BEGIN


SET @SQL = 'BCP "SELECT Fotografija FROM [Savez].[dbo].[Igraci] WHERE BrojIskaznice = "' + CAST(@ID as varchar(10)) + ' queryout "C:\ImagesFromSql\' + CAST(@ID as varchar(10)) + '.jpg" -T -fC "E:\formatfile-n.fmt'

EXEC master..xp_cmdshell  @SQL

FETCH NEXT FROM csr INTO @ID

END


CLOSE csr DEALLOCATE csr

And here is my full code when Images has original size. But they can not be opened at all! like they are corrupted, that's the reason why I'm coming back to this post, but I did this already! changed value to zero!

EXEC sp_configure 'show advanced options', 1 

GO  RECONFIGURE  GO  EXEC sp_configure 'xp_cmdshell', 1  GO  RECONFIGURE  GO

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

DECLARE @ID as int DECLARE @SQL as varchar(4000)

DECLARE  csr CURSOR FOR SELECT BrojIskaznice FROM [Savez].[dbo].[Igraci]

OPEN csr

FETCH NEXT FROM csr INTO @ID

WHILE @@FETCH_STATUS = 0  BEGIN


SET @SQL = 'BCP "SELECT Fotografija FROM [Savez].[dbo].[Igraci] WHERE BrojIskaznice = "' + CAST(@ID as varchar(10)) + ' queryout "C:\ImagesFromSql\' + CAST(@ID as varchar(10)) + '.jpg" -T -N'

EXEC master..xp_cmdshell  @SQL

FETCH NEXT FROM csr INTO @ID

END


CLOSE csr DEALLOCATE csr

I am really stuck here, this is so annoying to work with, does anyone have any advice?

Community
  • 1
  • 1

2 Answers2

0

I don't now the reason but BCP is not working fine with exporting images. The Best Way to export Images from SQL is to Create a DotNet application, Loading the images in a DataSet and Export them to files . There a lot of articles showing how to do it.Example 1 Example 2

But for TSQL :

Try the solution writen in this Topic

And for additional information read this Topic and read the comments at the bottom of the correct answer. We have made a lot of workarounds.

Community
  • 1
  • 1
Hadi
  • 36,233
  • 13
  • 65
  • 124
0

In the second code block for images with original size, it seems you are not specifying a format file

NB: I don't have enough reputation to comment.

If you are not trying to export just the image column, in fact there is no need for a lengthy query or even a format file. Just use the bcp to export the whole database table to the data file specified in the path.

Declare @sql varchar(500)
SET @sql = 'bcp Your_db.Your_table out Your_File_Path(eg: C:\ImagesNames.Dat) -T -S ' + @@SERVERNAME
EXEC @sql

Please see the relevant link below:
https://msdn.microsoft.com/en-us/library/ms191232(v=sql.110).aspx

Jaggan_j
  • 488
  • 1
  • 8
  • 9