SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[save2file]
AS
-- original script http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754 by "Peso"
-- modified script http://www.atwork.at/download/save2file.txt by Toni Pohl
declare @ObjectToken INT
declare @TheImage VARBINARY(MAX)
declare @Save2File VARCHAR(255)
declare @id int
declare @FileName varchar(255)
declare @Helper varchar(10)

DECLARE TheCursor CURSOR FAST_FORWARD FOR 
SELECT FIL_ID, rtrim(FIL_FileName), FIL_Content -- OR: FIL_Thumbnail 
FROM MyFiles ORDER BY FIL_ID ASC
		
OPEN TheCursor 
FETCH NEXT FROM TheCursor INTO @id, @FileName, @TheImage

WHILE @@FETCH_STATUS = 0
	BEGIN
		SET @Helper = right('00000' + rtrim(convert(varchar, @id)), 5)
		-- Pfad anpassen!
		SET @Save2File = 'D:\Temp\bin\' + @Helper + '_' + @FileName
		PRINT @Save2File

		EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT -- Creates an instance of an OLE object.
		EXEC sp_OASetProperty @ObjectToken, 'Type', 1 -- Sets a property of an OLE object to a new value.
		EXEC sp_OAMethod @ObjectToken, 'Open' -- Calls a method of an OLE object.
		EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @TheImage
		EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @Save2File, 2
		EXEC sp_OAMethod @ObjectToken, 'Close'
		EXEC sp_OADestroy @ObjectToken	-- Destroys a created OLE object.

		FETCH NEXT FROM TheCursor INTO @id, @FileName, @TheImage
	END 

CLOSE TheCursor
DEALLOCATE TheCursor
GO