How to save (all) SQL server image datatype to file system with correct file extension

Question

I have different types of files in table (txt, doc, jpg, etc) and I want to save them all to my file system. I know how to do it file by file, but it's no use, I have to do a loop that saves all files with right extensions at once. Any advice?

I do it this way now:

DECLARE @ObjectToken INT
EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
EXEC sp_OASetProperty @ObjectToken, 'Type', 1
EXEC sp_OAMethod @ObjectToken, 'Open'
EXEC sp_OAMethod @ObjectToken, 'Write', NULL, <this is where binary data goes>
EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'd:/file.txt', 2
EXEC sp_OAMethod @ObjectToken, 'Close'
EXEC sp_OADestroy @ObjectToken

So, my question would be is there a way to do this for all records (save all files) in db table at once and with setting the correct file extension for every saved file?

Solution 1

I resolved the problem. As I found it is REALLY not easy to migrate db from SQL Server 2000 to 2005. Only solution which works is this one - http://tek-works.com/fixing-there-is-already-an-object-named-sysnsobjs-in-the-database/

After completing those steps you just have to set Compatibility level to 90. (Right click on database > Properties > Compatibility level)

Then you can use VARBINARY(MAX) and save complete image datatype binary files to filesystem.

Good luck with migrating!

Solution 2

Based on the code I mentioned in the comments, AND assuming you use SQL Server 2005 or higher, this solution should work for you

DECLARE 
    @FileContent VARBINARY(MAX),
    @FileName VARCHAR(MAX),
    @ObjectToken INT,
    @FileID BIGINT

DECLARE cFiles CURSOR FAST_FORWARD FOR
SELECT Id, FileName, Image_Data from MyFileTable
OPEN cFiles

FETCH NEXT FROM cFiles INTO @FileID, @FileName, @FileContent

WHILE @@FETCH_STATUS = 0 BEGIN

    -- CHOOSE 1 of these SET statements:

    -- if FileName is unique, then just remove special characters
    SET @FileName = REPLACE(REPLACE(@FileName, '\', ' '), ':', ' ')
    -- if FileName without path information is unique
    SET @FileName = SUBSTRING(@FileName,
        len(@FileName)+2-CHARINDEX('\',REVERSE(@FileName)),len(@FileName))
    -- if you just take ID + extensions
    SET @FileName = 'c:\path\to\' + CAST(@FileID AS varchar) + 
        SUBSTRING(@ap_description,len(@ap_description)+1
            -CHARINDEX('.',REVERSE(@ap_description)),len(@ap_description))

    EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @FileContent
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FileName, 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

    FETCH NEXT FROM cFiles INTO @FileID, @FileName, @FileContent
END

CLOSE cFiles
DEALLOCATE cFiles

The image should be automatically cast to varbinary(max).

('\' breaks SO syntax highlighting.)