--This is the way to execute below SP  
  
  
---- Dont make any changes in the CONFIGURATION  
---- These configurations are required to create physical files in the specified location  
  
--sp_configure 'show advanced options', 1;  
--RECONFIGURE;  
--GO  
--sp_configure 'Ole Automation Procedures', 1;  
--RECONFIGURE;  
--GO  
  
--EXEC MIG_FILE_IMAGES_EXPORT_Sp  '', '', '', 0
--GO  
  
--sp_configure 'Ole Automation Procedures', 0;  
--RECONFIGURE;  
--GO  
--sp_configure 'show advanced options', 0;  
--RECONFIGURE;  
--GO  
  
  
CREATE OR ALTER  PROCEDURE MIG_FILE_IMAGES_EXPORT_Sp @Path nvarchar(1024), @FromDate date, @ToDate date, @ReGenerate bit  
AS     
BEGIN    
    
	--SET @FromDate = ISNULL(@FromDate, CONVERT(DATE,'01/01/1900') )
	--SET @ToDate = ISNULL(@ToDate, GETDATE() )

	Declare @SqlQry NVARCHAR(max), @WhereClause VARCHAR(max)

			if (IsNull(@FromDate,'') <> '' AND ISNULL(@ToDate,'') <> '')
            
                SET @whereClause = ' Where (IMG.CREATED_ON BETWEEN @FromDate AND @ToDate ) OR (IMG.UPDATED_ON BETWEEN @FromDate AND @ToDate) '
            
            else if (ISNULL(@FromDate,'') = '' AND ISNULL(@ToDate,'') = '')
            
                SET @whereClause = ''
            
            else if (IsNull(@FromDate,'') = '' AND  ISNULL(@ToDate,'') <> '')
            
                SET @whereClause = ' Where (IMG.CREATED_ON <= @ToDate) OR (IMG.UPDATED_ON <= @ToDate) '
            
            else if (IsNull(@FromDate,'') <> '' AND  ISNULL(@ToDate,'') = '')
            
                SET @whereClause = ' Where (IMG.CREATED_ON >= @FromDate) OR (IMG.UPDATED_ON >= @FromDate) '
 
	 Declare  @TMP_FILE_IMAGES TABLE([FILE_NAME] VARCHAR(48), [THUMBNAIL] image)
            
	 SET @SqlQry =  'SELECT CONCAT([GUID],''.'', MIG.THUMBNAIL_TYPE ) as [FILE_NAME], IMG.THUMBNAIL    
					 FROM MIG_FILE_IMAGES MIG    
					  INNER JOIN FILE_IMAGES IMG    
					  ON MIG.DSET_KEY = IMG.DSET_KEY AND     
					   MIG.DSET_REVISION = IMG.DSET_REVISION AND     
					   MIG.OBJECT_TYPE = IMG.OBJECT_TYPE AND     
					   MIG.OBJECT_KEY = IMG.OBJECT_KEY AND     
					   MIG.OBJECT_REVISION = IMG.OBJECT_REVISION  ' + @whereClause 
						--Where (IMG.CREATED_ON BETWEEN @FromDate AND @ToDate ) OR (IMG.UPDATED_ON BETWEEN @FromDate AND @ToDate) '
	
	INSERT INTO @TMP_FILE_IMAGES ([FILE_NAME], [THUMBNAIL])
	EXEC sp_executesql @SqlQry, N'@FromDate date, @ToDate date', @FromDate, @ToDate

	 DECLARE CURSOR_Files CURSOR FOR ( 
		
		SELECT [FILE_NAME], [THUMBNAIL] FROM  @TMP_FILE_IMAGES
	) 
	
 DECLARE @Filename VARCHAR(255) ;    
 DECLARE @ImageData varbinary(max);   
 --DECLARE @Path nvarchar(1024);   
  
 EXEC sp_configure 'show advanced options', 1;  
 RECONFIGURE;  
  
 EXEC sp_configure 'Ole Automation Procedures', 1;  
 RECONFIGURE;  
  
 OPEN CURSOR_Files    
    
 FETCH NEXT FROM CURSOR_Files INTO @Filename, @ImageData    
 WHILE (@@FETCH_STATUS <> -1)    
 BEGIN    
     
  -- SELECT @ImageData = (SELECT convert(varbinary(max), THUMBNAIL, 1) FROM FILE_IMAGES WHERE CONCAT(DSET_KEY,'_',DSET_REVISION,'.', THUMBNAIL_TYPE ) = @fileName);    
    
 --  DECLARE @Path nvarchar(1024);    
 IF ISNULL(@Path,'') = ''   
  SET @Path = 'c:\temp\img_files\';    
    
 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, @ImageData;    
 EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, @FullPathToOutputFile, 2;    
 EXEC sp_OAMethod @ObjectToken, 'Close';    
 EXEC sp_OADestroy @ObjectToken;    
    
   FETCH NEXT FROM CURSOR_Files INTO @Filename, @ImageData    
 END    
 CLOSE CURSOR_Files    
 DEALLOCATE CURSOR_Files    
  
 EXEC sp_configure 'Ole Automation Procedures', 0;  
 RECONFIGURE;  
 EXEC sp_configure 'show advanced options', 0;  
 RECONFIGURE;  
END  