Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.sqlserver.programming > #31291

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

Show all headers | View raw

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

Back to microsoft.public.sqlserver.programming | Previous | Next — Next in thread | Find similar

Thread

While FTP'g the archive attribute appears sometimes preventing me from renaming the filename using T-SQL George Lewycky <gelewyc@nyct.com> - 2016-02-29 14:50 -0800
  Re: While FTP'g the archive attribute appears sometimes preventing me from renaming the filename using T-SQL Erland Sommarskog <esquel@sommarskog.se> - 2016-03-01 21:37 +0100

csiph-web