>>Script Language and Platform: SQL Server
This script displays the size of a given file.
–Usage:
select dbo.udf_CheckFileSize (”d:sqldumpstestlatency_20041206_08_00_07.TRN”)
select dbo.udf_CheckFileSize (”d:sqldumpsmsdbx.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