>>Script Language and Platform: SQL Server
This script displays the size of a given file.
--Usage: select dbo.udf_CheckFileSize (''d:\sqldumps\testlatency_20041206_08_00_07.TRN'') select dbo.udf_CheckFileSize (''d:\sqldumps\msdbx.bak'') select dbo.udf_CheckFileSize (''x:\sqldumpsmsd.bak'') --Results 145920 bytes 23092736 bytes -1 means File not found or path not found -2 error during FSO process
Author: MAK
Create function udf_CheckFileSize (@filename varchar(1000)) returns bigint as --Created by : MAK --Date: Dec 5, 2004 --Objective: To display the size of the given file BEGIN DECLARE @FS int DECLARE @OLEResult int DECLARE @FileID int DECLARE @Size bigint DECLARE @Flag bigint set @size =0 set @Flag =0 EXECUTE @OLEResult = sp_OACreate 'Scripting.FileSystemObject', @FS OUT EXECUTE @OLEResult = sp_OAMethod @FS, 'GetFile', @FileID OUT,@filename IF @OLEResult <> 0 begin set @Flag =-1 end else begin EXECUTE @OLEResult = sp_OAGetProperty @FileId,'Size', @Size OUT IF @OLEResult <> 0 begin set @Flag =-2 end end EXECUTE @OLEResult = sp_OADestroy @FileID EXECUTE @OLEResult = sp_OADestroy @FS if @flag <> -1 and @flag <> -2 begin set @flag = @size end return @flag END
Disclaimer: We hope that the information on these script pages is
valuable to you. Your use of the information contained in these pages,
however, is at your sole risk. All information on these pages is provided
"as -is", without any warranty, whether express or implied, of its accuracy,
completeness, or fitness for a particular purpose...
Disclaimer Continued
Back to Database Journal Home