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?

• Ram Ram

  Ten Centuries

  Points: 1079

  More actions

  January 16, 2007 at 9:45 am

  #683532

  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

  January 16, 2007 at 3:37 pm

  #683633

  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

  January 17, 2007 at 1:41 am

  #683665

  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

  January 18, 2007 at 3:15 am

  #683941

  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)