How to extract files from SQL Server blob fields into files.

— Write all database files (pdf) to file.
——— ——— ——— ——— ——— ——— ———
DECLARE CURSOR_Id CURSOR FOR (SELECT [RecId] FROM [dbo].[SYSOUTGOINGEMAILDATA])

DECLARE @RecId BIGINT;

OPEN CURSOR_Id

FETCH NEXT FROM CURSOR_Id INTO @RecId
WHILE (@@FETCH_STATUS <> -1)
BEGIN
DECLARE @Data varbinary(max);
SELECT @Data = (SELECT convert(varbinary(max), Data, 1) FROM [dbo].[SYSOUTGOINGEMAILDATA] WHERE RecId = @RecId);

DECLARE @Path nvarchar(1024);
SELECT @Path = ‘C:\MyPDFs\Output’;

DECLARE @Filename NVARCHAR(1024);
SELECT @Filename = (SELECT [Filename] + [FILEEXTENSION] AS [PDFFile] FROM SYSOUTGOINGEMAILDATA WHERE RecId = @RecId);

DECLARE @FullPathToOutputFile NVARCHAR(2048);
SELECT @FullPathToOutputFile = @Path + ‘\’ + @Filename;

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, @Data;
EXEC sp_OAMethod @ObjectToken, ‘SaveToFile’, NULL, @FullPathToOutputFile, 2;
EXEC sp_OAMethod @ObjectToken, ‘Close’;
EXEC sp_OADestroy @ObjectToken;

FETCH NEXT FROM CURSOR_Id INTO @RecId
END
CLOSE CURSOR_Id
DEALLOCATE CURSOR_Id

— Make sure the following statement is executed to enable file IO
— From http://msdn.microsoft.com/en-us/library/ms191188.aspx
——— ——— ——— ——— ——— ——— ———
/*
sp_configure ‘show advanced options’, 1;
GO
RECONFIGURE;
GO
sp_configure ‘Ole Automation Procedures’, 1;
GO
RECONFIGURE;
GO
*/