PABluesMan
Starting Member
26 Posts |
Posted - 03/25/2008 : 16:46:50
|
Here's a table function that returns a table variable containing various file properties (creation date, size, etc.). The one limitation to remember is that it won't work with files on mapped drives; you have to use the full \\<Server>\<Share>\<Folder1..Folder n>\<Filename> syntax. Still, it's a pretty useful thing to have around. :)
/****************************************************************************/ CREATE FUNCTION fnc_GetFileProps
(@FileName VARCHAR (1024))
RETURNS @Results TABLE ( ErrorCode TINYINT DEFAULT (0), PropName VARCHAR (255), PropValue SQL_VARIANT )
AS BEGIN DECLARE @OLEResult INT DECLARE @FS INT DECLARE @FileID INT DECLARE @Message VARCHAR (8000) DECLARE @ErrorSource VARCHAR (255) DECLARE @ErrorDesc VARCHAR (255) DECLARE @INT INT DECLARE @VARCHAR VARCHAR (1024) DECLARE @DATETIME DATETIME DECLARE @BIGINT BIGINT
-- Create an instance of the file system object EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT IF @OLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @FS, @ErrorSource OUT, @ErrorDesc OUT
INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, @ErrorSource, @ErrorDesc)
RETURN END
EXEC @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT, @Filename IF @OLEResult <> 0 BEGIN EXEC sp_OAGetErrorInfo @FS, @ErrorSource OUT, @ErrorDesc OUT
INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, @ErrorSource, @ErrorDesc)
RETURN END
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Attributes', @INT OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'Attributes', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('Attributes', @INT)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateCreated', @DATETIME OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'DateCreated', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('DateCreated', @DATETIME)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateLastAccessed', @DATETIME OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'DateLastAccessed', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('DateLastAccessed', @DATETIME)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'DateLastModified', @DATETIME OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'DateLastModified', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('DateLastModified', @DATETIME)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Name', @VARCHAR OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'Name', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('Name', @VARCHAR)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Path', @VARCHAR OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'Path', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('Path', @VARCHAR)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'ShortPath', @VARCHAR OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'ShortPath', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('ShortPath', @VARCHAR)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Size', @BIGINT OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'Size', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('Size', @BIGINT)
EXEC @OLEResult = sp_OAGetProperty @FileID, 'Type', @VARCHAR OUT IF @OLEResult <> 0 BEGIN INSERT @Results (ErrorCode, PropName, PropValue) VALUES (1, 'Type', '<ERROR RETRIEVING PROPERTY>') END ELSE INSERT @Results (PropName, PropValue) VALUES ('Type', @VARCHAR)
EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FS
RETURN END /****************************************************************************/
I geek, therefore I am |
|