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

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

X-Received	by 10.50.65.69 with SMTP id v5mr328643igs.13.1456786204170; Mon, 29 Feb 2016 14:50:04 -0800 (PST)
X-Received	by 10.50.164.130 with SMTP id yq2mr12646igb.6.1456786204124; Mon, 29 Feb 2016 14:50:04 -0800 (PST)
Path	csiph.com!weretis.net!feeder6.news.weretis.net!news.glorb.com!ok5no5442182igc.0!news-out.google.com!pn7ni6063igb.0!nntp.google.com!hb3no10229118igb.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
Newsgroups	microsoft.public.sqlserver.programming
Date	Mon, 29 Feb 2016 14:50:03 -0800 (PST)
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=170.28.128.49; posting-account=Vnxw1AkAAAAhX6hQW7OO0eAPRfjDcoxh
NNTP-Posting-Host	170.28.128.49
User-Agent	G2/1.0
MIME-Version	1.0
Message-ID	<7b633239-aa13-412f-8672-51bf3b46f89e@googlegroups.com> (permalink)
Subject	While FTP'g the archive attribute appears sometimes preventing me from renaming the filename using T-SQL
From	George Lewycky <gelewyc@nyct.com>
Injection-Date	Mon, 29 Feb 2016 22:50:04 +0000
Content-Type	text/plain; charset=ISO-8859-1
Content-Transfer-Encoding	quoted-printable
Xref	csiph.com microsoft.public.sqlserver.programming:31291

Show key headers only | 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