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

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

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

Back to article view | Back to microsoft.public.sqlserver.programming

Contents

  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

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

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

[toc] | [next] | [standalone]

#31292

George Lewycky (gelewyc@nyct.com) writes:
> 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.


-- 
Erland Sommarskog, Stockholm, esquel@sommarskog.se

[toc] | [prev] | [standalone]

Back to top | Article view | microsoft.public.sqlserver.programming

csiph-web