How to dump all of our images from a VARBINARY(MAX) field in SQL Server 2008 to the filesystem?

Question

I am confused about difference between IMDT and Intel DCPMM's memory mode. I understood that both technology use DRAM as an extra L4 Cache, and Intel DCPMM's memory mode makes DRAM non-addressable (which means system memory is only shown as sum of DCPMMs' capacity). I referred link below.

https://venturebeat.com/2019/12/09/optane-essentials-part-2-memory-modes-and-optimal-workloads/

So my question is, does IMDT also make system DRAM non-addressable? Moreover, when I use IMDT, does system DRAM shown as system memory count?

Thanks.

Solution

I've figured it out thanks to this post ...

SET NOCOUNT ON

DECLARE @IdThumbnail INTEGER,
        @MimeType VARCHAR(100),
        @FileName VARCHAR(200),
        @Sqlstmt varchar(4000)


DECLARE Cursor_Image CURSOR FOR
    SELECT a.IdThumbnail
    FROM tblThumbnail a
    ORDER BY a.IdThumbnail

OPEN Cursor_Image
    FETCH NEXT FROM Cursor_Image INTO @IdThumbnail

    WHILE @@FETCH_STATUS = 0
    BEGIN

        -- Generate the file name based upon the ID and the MIMETYPE.
        SELECT @FileName = LTRIM(STR(@IdThumbnail)) + '.png'

        -- Framing DynamicSQL for XP_CMDshell            
        SET @Sqlstmt='BCP "SELECT OriginalImage 
                      FROM Appian.dbo.tblThumbnail 
                      WHERE IdThumbnail = ' + LTRIM(STR(@IdThumbnail)) +
                      '" QUERYOUT c:\Temp\Images\' + LTRIM(@FileName) + 
                      ' -T -fC:\Temp\ImageFormatFile.txt'
        print @FileName
        print @sqlstmt

        EXEC xp_cmdshell @sqlstmt
        FETCH NEXT FROM Cursor_Image INTO @IdThumbnail
    END

CLOSE Cursor_Image
DEALLOCATE Cursor_Image

Please note -> u need to have a format file, for the BCP command. This is the content of the file and i've placed it in c:\Temp (as noted in the BCP commandline above).

10.0
1
1       SQLIMAGE            0       0       ""   1     OriginalImage                      ""

Final note about that format file .. there HAS TO BE A NEW LINE AFTER THE LAST LINE. otherwise u'll get an error.

Enjoy!