How Do You Save An Image Out of a SQL Database Into the File System Using only SQL?

Question

Similar to this question only the other way of flow.

http://stackoverflow.com/questions/416881/insert-picture-into-sql-server-2005-image-field-using-only-sql

I need to be able to save a image field out into the file system and be able to name the file with only using SQL. I don't want to use TEXTCOPY either because I need to use the connection to the database that is running the query itself.

I'd like this to work in SQL 2005 to support older database clients but if it's available in only 2008 that's fine.

Any ideas?

Answer 1

I don't believe there is a way to save out using only sql, but there is a command-line utility, bcp.

Answer 2

With SQL 2k8 there is the new FILESTREAM type that covers such cases. Filestreams can be opened via the Win32 file access handle like any other file, but hey are integrated into the database from transaction and backup/restore point of view.

I had a similar issue in SQL 2k5 and my solution was to use a CLR stored procedure with EXTERNAL_ACCESS that was writing into the file system using C# file operations.