CREATE proCEDURE [dbo].[sp_Pic_SaveToFile] (@ImageData Image, @Filename as nvarchar(1024))
AS
-- =============================================
-- Author: pakzad
-- Created date: 95/06/19
-- Updated : 
-- Description:	
---  sp_Pic_SaveToFile 0xaaaaabbbbbbbccccccc, 'c:\temp\1\fxx1.jpg'
---
--- save image or varbinary(max) to file with adodb.stream
-- =============================================

/*
			-- Write database image (jpg) to file
			-- http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101754

			http://stackoverflow.com/questions/1366544/how-to-export-image-field-to-file

			adod.stream:
			http://www.w3schools.com/asp/ado_ref_stream.asp
			https://support.microsoft.com/en-us/kb/276488
			Const adTypeBinary = 1



			-- 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


			EXEC sp_configure 'show advanced options', 1;  
			GO  
			RECONFIGURE;  
			GO
			EXEC sp_configure 'xp_cmdshell',1  
			GO  
			RECONFIGURE;  
			GO


			sp_configure 'show advanced options', 1
			reconfigure with override
			sp_configure 'Ad Hoc Distributed Queries', 1 
			reconfigure with override


*/


BEGIN
	SET NOCOUNT ON;
	

	DECLARE @ObjectReturn INT
	DECLARE @ObjectToken INT

	DECLARE @ErrorNumber INT
	DECLARE @ErrorSeverity INT
	DECLARE @ErrorState INT
	DECLARE @ErrorProcedure NVARCHAR(126)
	DECLARE @ErrorLine INT
	DECLARE @ErrorMessage NVARCHAR(2048)

	DECLARE @ErrorSource VARCHAR(255)
	DECLARE @ErrorDesc VARCHAR(255)

	DECLARE @FileHandle INT

begin try
	EXEC @ObjectReturn=sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT;
		IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @ObjectToken, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('ADODB.Stream Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END

	EXEC @ObjectReturn=sp_OASetProperty @ObjectToken, 'Type', 1; --adTypeBinary = 1  , adTypeText=2
	EXEC @ObjectReturn=sp_OAMethod @ObjectToken, 'Open';
		IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Open Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END
	EXEC @ObjectReturn=sp_OAMethod @ObjectToken, 'Write', NULL, @ImageData;
		IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Write Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END
	EXEC @ObjectReturn=sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @Filename, 2;  --adSaveCreateOverWrite=2
		IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('SaveToFile Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END
	EXEC @ObjectReturn=sp_OAMethod @ObjectToken, 'Close';
		IF (@ObjectReturn <> 0)
        BEGIN
            EXEC sp_OAGetErrorInfo @FileHandle, @ErrorSource OUTPUT, @ErrorDesc OUTPUT 
            RAISERROR('Close Error (return: ''%u'', source: ''%s'', description: ''%s'')', 15, 1, @ObjectReturn, @ErrorSource, @ErrorDesc)
        END
	EXEC @ObjectReturn=sp_OADestroy @ObjectToken;
END TRY
BEGIN CATCH
    SELECT
        @ErrorNumber = ERROR_NUMBER(),
        @ErrorSeverity = ERROR_SEVERITY(),
        @ErrorState = ERROR_STATE(),
        @ErrorProcedure = ERROR_PROCEDURE(),
        @ErrorLine = ERROR_LINE(),
        @ErrorMessage = ERROR_MESSAGE();
    RAISERROR('Procedure ''%s'' failed on line number ''%u'' with message ''%s'' - (error number: ''%u'', severity: ''%u'', state: ''%u'').', 15, 1, @ErrorProcedure, @ErrorLine, @ErrorMessage, @ErrorNumber, @ErrorSeverity, @ErrorState)
END CATCH
END