Technical Article

fn_getfiledetails - a replacement for xp_getfiledetails

,

For this function to work you need to enable

- OLE Automation

and

- xp_cmdshell

in the SQL Server 2005 Surface Area Configuration.

 

Also the sql server service account needs the permission to access the concerning file.

 

 

 

 

-- =============================================
-- Author:Karl Klingler
-- Create date: 20100507
-- Description:Based on the code from Phil Factor
-- found on http://www.simple-talk.com/sql/learn-sql-server/building-my-first-sql-server-2005-clr/
--              i had to eliminate the error reporting because that will not work in functions
-- =============================================
CREATE FUNCTION fn_getfiledetails 
(
@Filename sysname
)
/*
Example: select [DateLastModified] from fn_getfiledetails('c:\autoexec.bat')
*/RETURNS 
@filedetails TABLE 
(
[Path] VARCHAR(100),
[ShortPath] VARCHAR(100),
[Type] VARCHAR(100),
[DateCreated] datetime,
[DateLastAccessed] datetime,
[DateLastModified] datetime,
[Attributes] INT,
[size] INT
)

AS
BEGIN
DECLARE 
@hr INT,-- the HRESULT returned from the FileSystem object
@objFileSystem INT,--
@objFile INT,-- the File object
@Path VARCHAR(100),--
@ShortPath VARCHAR(100),
@Type VARCHAR(100),
@DateCreated datetime,
@DateLastAccessed datetime,
@DateLastModified datetime,
@Attributes INT,
@size INT

EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @objFileSystem OUT

IF @hr=0 EXEC @hr = sp_OAMethod @objFileSystem, 'GetFile',  @objFile out,@Filename

IF @hr=0 EXEC @hr = sp_OAGetProperty @objFile, 'Path', @path OUT
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

EXEC sp_OADestroy @objFileSystem
EXEC sp_OADestroy @objFile

INSERT @filedetails
SELECT [Path]=  @Path,
   [ShortPath]=    @ShortPath,
   [Type]= @Type,
   [DateCreated]=  @DateCreated ,
   [DateLastAccessed]=     @DateLastAccessed,
   [DateLastModified]=     @DateLastModified,
   [Attributes]=   @Attributes,
   [size]= @size
RETURN
END

Rate

3.5 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3.5 (2)

You rated this post out of 5. Change rating