3. Check File Size

1.사용법

--Usage:
select dbo.udf_CheckFileSize (''c:\choiwonwoo.txt'')
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


2. script

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