>>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