drop procedure tsql_getfiledetails_OLE
go
CREATE PROCEDURE tsql_getfiledetails_OLE
/* The base code for this SP came from xxxxx.  The original code
was modified by Gregory A. Larsen to return exactly same results as the
depricated extended stored procedure xp_getfile details.
*/
@Filename VARCHAR(100)

/*
spFileDetails 'c:\autoexec.bat'
*/
AS
DECLARE @hr INT,         --the HRESULT returned from 
       @objFileSystem INT,              --the FileSystem object
       @objFile INT,            --the File object
       @ErrorObject INT,        --the error object
       @ErrorMessage VARCHAR(255),--the potential error message
       @Path VARCHAR(100),
       @ShortPath VARCHAR(100),
       @Type VARCHAR(100),
       @DateCreated datetime,
       @DateLastAccessed datetime,
       @DateLastModified datetime,
       @Attributes INT,
       @size INT



SET nocount ON

SELECT @hr=0,@errorMessage='opening the file system object '
EXEC @hr = sp_OACreate 'Scripting.FileSystemObject',
                                       @objFileSystem OUT
IF @hr=0 SELECT @errorMessage='accessing the file '''
                                       +@Filename+'''',
       @ErrorObject=@objFileSystem
IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem,
         'GetFile',  @objFile out,@Filename
IF @hr=0 
       SELECT @errorMessage='getting the attributes of '''
                                       +@Filename+'''',
       @ErrorObject=@objFile
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'ShortPath', @ShortPath OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'Type', @Type OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateCreated', @DateCreated OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateLastAccessed', @DateLastAccessed OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'DateLastModified', @DateLastModified OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'Attributes', @Attributes OUT
IF @hr=0 EXEC @hr = sp_OAGetProperty 
             @objFile, 'size', @size OUT


IF @hr<>0
       BEGIN
       DECLARE 
               @Source VARCHAR(255),
               @Description VARCHAR(255),
               @Helpfile VARCHAR(255),
               @HelpID INT
       
       EXECUTE sp_OAGetErrorInfo  @errorObject, 
               @source output,@Description output,
                               @Helpfile output,@HelpID output

       SELECT @ErrorMessage='Error whilst '
                               +@Errormessage+', '
                               +@Description
       RAISERROR (@ErrorMessage,16,1)
       END
EXEC sp_OADestroy @objFileSystem
EXEC sp_OADestroy @objFile

-- convert @Path to return just the file name not the full path name
set @Path = replace(@Path,'/','\')
set @Path = substring(@Path,len(@path)-charindex('\',reverse(@Path))+2,len(@path)) 

SELECT [Alternate Name]=  @Path,
	   [Size]= @size,
       [Creation Date] = convert(char(8),@DateCreated,112) ,
       [Creation Time] = replace(convert(char(8),@DateCreated,108),':','') ,
       [Last Written Date] = convert(char(8),@DateLastModified,112) ,
       [Last Written Time] = replace(convert(char(8),@DateLastModified,108),':','') ,
       [Last Accessed Date] = convert(char(8),@DateLastAccessed,112) ,
       [Last Accessed Time] = replace(convert(char(8),@DateLastAccessed,108),':','') ,
       [Attributes]=   @Attributes
       
RETURN @hr