George Lewycky
unread,Feb 29, 2016, 5:48:44 PM2/29/16You do not have permission to delete messages in this group
Sign in to report message
Either email addresses are anonymous for this group or you need the view member email addresses permission to view the original message
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