how to Get File Size of Text File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

how to Get File Size of Text File

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-09-29 : 08:19:24

Rik writes "Hi,

I would like to get file sixe of Text file using SQL Stored Procedure.
Is there an Easy way to get the File Size?

Thanks In advance"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-09-29 : 08:39:38

You can use Front End application with FileSystem object

Here is an unreliable method

master..xp_getfiledetails 'path_of_the_file'

Madhivanan

Failing to plan is Planning to fail

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-09-29 : 09:48:14

If you are a sysadmin, you can use the File System Object in a stored procedure:


declare @return        int 
declare @fso           int
declare @fso_file      int
declare @fso_file_size int
declare @Filename varchar(1000)

-- Create File SystemObject
execute @return = sp_OACreate
	'Scripting.FileSystemObject',
	@fso    output

select @Filename = 'c:\winnt\explorer.exe'

-- Create File Object
execute @return = sp_OAMethod
	@fso,
	'GetFile',
	@fso_file    output,
	@Filename

-- Get File Size
execute @return = sp_OAGetProperty
	@fso_file,
	'Size',
	@fso_file_size    output

-- Dispaly file size
select file_size = @fso_file_size

-- Create directory command
select @Filename = 'dir '+@Filename

-- Use command shell to get file size with directory command to verify
exec master.dbo.xp_cmdshell @Filename

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources