Roxy'Pro Roxy'Pro - 1 month ago 16
SQL Question

Export images from a SQL Server using Stored procedures

I'm using Microsoft Sql server 2012 as DBMS, there I have my database which is containing informations about football players. Each player has a photography, and I need to export players photos to my computer, I guess it is possible to do it over TSQL, so I could avoid programming or editing my application to export photos only.

Here is what I did so far:

enter image description here

MY SQL CODE:


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'

EXEC master..xp_cmdshell 'BCP "SELECT Photo FROM
[FootballTeam].[dbo].[Players]" queryout
"C:\ImagesFromSql\TestImage.jpg" -T -N'


As you can see, my folder ImagesFromSql is created on "C:\", also one photo is stored there but intersting is that photo is 361MB big so probably that is size of all photos that are contained in database?
And I am wondering how could I export all images from database instead of one in this case, and is it possible to set their names for example lets set Image name as PlayerID, for example 1.jpg, 2.jpg, 3.jpg....650.jpg...

after @H.Fadlallah suggested some answers this is what I get: enter image description here

It is impossible to preview image.. :)

Answer

try the following query:

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 ID FROM [FootballTeam].[dbo].[Players]

OPEN csr

FETCH NEXT FROM csr INTO @ID

WHILE @@FETCH_STATUS = 0 
BEGIN


SET @SQL = 'BCP "SELECT Photo FROM [FootballTeam].[dbo].[Players] WHERE ID = "' + 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