when FTP a file the "archive" attribute is sometimes set and I can't change the filename

4 views
Skip to first unread message

George Lewycky

unread,
Feb 29, 2016, 5:48:44 PM2/29/16
to

Hello

I have a batch job running on SQL Server to GET an ascii file from an AS/400 machine and after I process it I rename the file with a date stamp using Transaction SQL (T-SQL) under SQL Server

Its been running a few years without a hitch but this year the file won't rename and I'm suspecting its due to the "A"rchive attribute being set--but only its 5th time this year?


Upon researching I'm seeing two techniques of removing the Archive attibute before I try to rename the file using T-SQL. The "A" appears when I use DIR or when I look into the files advanced attributes and the checkbox is clicked for "file is ready for archiving"


get /home/timiprod/elestfr/elevescl.txt c:\ATSToday.txt


I'm tempted to just add the following command:

ATTRIB -A c:\ATSToday.txt


NOTE the rename occurs not with DOS but with SQL Servers sp_OACreate command:

DECLARE
@newname varchar( 250 );
SET @dt = GETDATE( );
---------------------------------------------------------------------------------------------------------------
SET @newname = 'ATS_Archive' + CONVERT( varchar( 15 ) ,@dt ,112 ) + '_' + REPLACE( CONVERT( char( 5 ) ,GETDATE( ) ,108 ) ,':' ,'' ) + '.txt';
EXEC @RetCode = sp_OACreate 'Scripting.FileSystemObject' ,@oFS OUTPUT;
IF @RetCode <> 0
BEGIN

EXEC sp_OAGetErrorInfo @oFS ,@src OUT ,@desc OUT;
RAISERROR( 'Object Creation Failed 0x%x, %s, %s' ,16 ,1 ,@retcode ,@src ,@desc );
RETURN;
END;


Thanks

George
Reply all
Reply to author
Forward
0 new messages