While FTP'g the archive attribute appears sometimes preventing me from renaming the filename using T-SQL

[George Lewycky]

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;


Any ideas or other suggestions?

Thanks

George

[Erland Sommarskog]

Post by George Lewycky
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?

My only comment is that this is good incentive to reimplement the operation
in Powershell or SSIS. This stuff should not run from SQL Server at all.
T-SQL is not a general-purpose programming language.