This requires that the “Ole Automation Procedures” option be enabled. See Setting and Restoring Configuration Settings for doing this.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
-- declare variables DECLARE @oADODB INT, @FilePath varchar(80), @ImageData varbinary(MAX); DECLARE cFiles CURSOR FOR SELECT FileAndPath = 'D:\'+FileName, --<< change to drive\path you want this saved to FileData FROM dbo.Attachments WHERE SourceId = 62; OPEN cFiles; FETCH NEXT FROM cFiles INTO @FilePath, @ImageData; WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_OACreate 'ADODB.Stream', @oADODB OUTPUT; EXEC sp_OASetProperty @oADODB, 'Type', 1; EXEC sp_OAMethod @oADODB, 'Open'; EXEC sp_OAMethod @oADODB, 'Write', NULL, @ImageData; EXEC sp_OAMethod @oADODB, 'SaveToFile', NULL, @FilePath, 2; EXEC sp_OAMethod @oADODB, 'Close'; EXEC sp_OADestroy @oADODB; FETCH NEXT FROM cFiles INTO @FilePath, @ImageData; END; CLOSE cFiles; DEALLOCATE cFiles; |
