How do I retrieve and image datatype from SQLServer 2005 without programmatic access from some other language.

Question

I have a SQL Server 2005 table with an image datatype as on of the table fields. I have a specific row I am interested. I'd like to write the binary data in the image field to a file on a network share.

Let's call the share \server\sharename

Answer 1

This worked for me. Obviously path names and instance names may need to be altered your end.

It requires master..xp_cmdshell enabled (but you could just run bcp directly if there is no need to do it in T-SQL)

EXEC master..xp_cmdshell 
'bcp "SELECT [LargePhoto] FROM [AdventureWorks2008].[Production].[ProductPhoto] WHERE [ProductPhotoID]=70" queryout C:\temp\YourImage.jpg -S(local)\SQL2008 -T -fC:\temp\test.fmt'

It requires the following format file C:\temp\test.fmt

9.0
1
1 SQLBINARY 0 0 "" 1 col1 ""