How to find the size of file in KB with sp_OAGetProperty

Question

How to find the size of file in KB with sp_OAGetProperty

Pulivarthi Sasidhar

SSCertifiable

Points: 6706
More actions
October 4, 2010 at 5:44 am

#142961

Hi,
How to get the size of the file using sp_OAGetProperty in KB.
i want to find the size of text file.
Thanks,
Pulivarthi

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

if the only thing you are after is file size, the DIR command is faster and easier to use to get it; now if you are after information like modified date, version information, and other stuff exposed by the FileSystemObject, sp_OaCreate might work, but there are easier methods via CLR to do that;

CREATE TABLE #Results (txtResult varchar(2000) NULL)

INSERT INTO #Results (txtResult)

--get path and file size information about all files in a given directory

EXEC master.dbo.xp_cmdshell 'DIR D:\*_BACKUP_*.*'

SELECT * FROM #Results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 2

here's a slight modification chopping up the results into file date,filesize and filename:

SELECT

SUBSTRING(txtResult,1,20) AS FileDate,

CASE

WHEN txtResult LIKE '%<DIR>%'

THEN NULL

ELSE LTRIM(RTRIM(SUBSTRING(txtResult,21,18)))

END AS FileSize,

CASE

WHEN txtResult LIKE '%<DIR>%'

THEN NULL

ELSE LTRIM(RTRIM(SUBSTRING(txtResult,40,2000)))

END AS FileName*

FROM #Results

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!