using dos commands to check external file sizes

Question

Post reply

using dos commands to check external file sizes

winston Smith

SSCoach

Points: 19500
More actions
January 16, 2007 at 8:58 am

#115688

one of my sql jobs has to move dat files from one location to another. The dat files come from an external vendor. currently what is done , the sql queries the source location using dos commands, as shown below

declare
@string varchar(50)
set
@string = 'dir c:\'
insert
into test
exec
master..xp_cmdshell @string

we now have a list of all the files on the source location, however, if the files are in the process of copying, i cannot copy them, and the job fails. i know that when files are being copied, they have a size of 0. is there any easy way to check the file size? i know it is returned using the dir command in a record like below, but that requires string manipulation , and i wanted to know if there was an easier way
11/02/2004 03:02 PM 307 m.txt

Also, is there a better way for getting files and moving them than using xp_cmdshell sproc and dos commands?

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ram Ram Ten Centuries Points: 1079 More actions · Answer 1

You may try like this if you know the filename for eg "C:\Text.xls"

declare @string varchar(50)

set

@string = 'FOR %T IN ("C:\Text.xls") DO ECHO %~zT'

insert into test

exec

master..xp_cmdshell @string

select * from test

Ram

dclark SSC Eights! Points: 843 More actions · Answer 2

You could use OA automation stored procedures with the Scripting.FileSystemObject

EXEC @ProcRC = sp_OACreate

'Scripting.FileSystemObject',

@fso output

-- create File Object

EXEC @ProcRC = sp_OAMethod

@fso,

'GetFile',

@fso_file output,

@FULL_FILE_NAME

-- Get File Size

EXEC @ProcRC = sp_OAGetProperty

@fso_file,

'Size',

@fso_file_size output

Vladan SSC-Insane Points: 22072 More actions · Answer 3

Another way to check files is

EXEC master..xp_getfiledetails @File_with_path

It gives you more than just size, maybe these other values can help you as well:

Alternate Name, Size, Creation Date, Creation Time, Last Written Date, Last Written Time, Last Accessed Date, Last Accessed Time, Attributes.

winston Smith SSCoach Points: 19500 More actions · Answer 4

is this possible with SMO? where can i find a bit more info on this? ive checked BOL, and had a look on the web but its all a bit vague ( for me anyway)