December 26, 2016 at 6:15 am
Hi! I have a procedure that:
Create Procedure MainProc as
Declare @dirxml Varchar( 300 )
Declare @dirend Varchar( 300 )
Declare @arqxmlVarchar( 300 )
Declare @xmlVarchar( MAX )
Declare @command Varchar( 500 )
Declare @fullnamexmlVarchar( 500 )
Declare @types Varchar( 2 )
Declare @sql
SET XACT_ABORT, NOCOUNT ON;
BEGIN TRY
BEGIN
SET @dirxml = 'C:\Users\XML'
SET @dirend = 'C:\Users\IMPORT\XML'
SET @command = 'dir ' + @dirxml + ' /B'
INSERT XMLTAB (XML_ARQ)
EXEC master.dbo.xp_cmdshell @command
DELETE FROM XMLTAB WHERE XML_ARQ IS NULL
While ( SELECT COUNT(*) FROM XMLTAB WHERE XML_FLG IS NULL ) > 0
Begin
SELECT TOP 1 @arqxml = XML_ARQ FROM XMLTAB WHERE XML_FLG IS NULL
SET @fullnamexml = RTRIM(@dirxml)+'\'+RTRIM(@arqxml)
SET @sql = N'SELECT @xmlOut = CAST(BulkColumn AS Varchar(MAX)) FROM OPENROWSET(BULK ''' + @fullnamexml + ''', SINGLE_BLOB) AS Arquivo'
EXEC sp_executesql @sql , N'@xmlOut Varchar(max) Output' ,@xmlOut = @xml OUTPUT
SET @types = SUBSTRING(@xml,CHARINDEX('<mod>', @xml)+5,2)
IF @types = '001'
begin
EXEC dbo.Proc1 @xml , @arqxml, @dirend = @dirend output
end
ELSE
begin
EXEC dbo.Proc2 @xml , @arqxml, @dirend = @dirend output
end
SET @command = 'move ' + @fullnamexml + ' ' + @dirdest + '\'+RTRIM(@dirdest1)
EXEC master.dbo.xp_cmdshell @command, no_output
DELETE FROM XMLTAB WHERE XML_ARQ = @arqxml
End
END TRY
BEGIN CATCH
WHILE @@TRANCOUNT > 0
BEGIN
ROLLBACK TRANSACTION;
END
END CATCH;
Before the new request, I inserted the names of the files into an XMLTAB table and was processing this file.
After the process was completed, it moved to an import folder, deleted the file name from the table, and repeated the process until all the files were processed.
Now, I need
1) Rename files names that are in folder MoveA appending datetime Ex: file1.txt -> file1_20161226_104230 (20161226 = YYYYMMDD and 104230 = HHMMSS)
2) Move the files from @dirxml ('C:\Users\XML') to folder MoveA
3) Importing files names to a table
3) Process the file
4) delete information that file was processesed with table
5) move files was processesed to folder IMPORT
Before a new request I only
1) Importing files names from @dirxml ('C:\Users\XML') to a table XMLTAB using xp_cmdhell 'dir /B'
2) Process the file
3) delete information that file was processesed with table
4) move files was processesed to folder IMPORT
Question are:
1) How can I rename files name append _date_time to name at once
2) How can I move files at other folders at once
I'm waiting for your help,
thanks
December 26, 2016 at 2:24 pm
You're already using xp_CmdShell. Search for the following DOS commands to take it to the next level.
REN
FORFILES
ROBOCOPY
Also, learn how to capture the OUTPUT of the xp_CmdShell call in a table to be able to do some pretty neat things like error checking, etc. Create a table with an IDENTITY column and a 500 character VARCHAR column. When you run xp_CmdShell, simply do an INSERT/EXEC to capture the output in the table.
CREATE TABLE #CmdResult
(
RowNum INT IDENTITY(1,1)
,CmdOutput VARCHAR(500)
)
;
INSERT INTO dbo.CmdResult
(CmdOutput)
EXEC xp_CmdShell @SomeCmdString
;
[EDIT] As a bit of a sidebar, I'm not sure why you think "all at once" is a good idea. Except for renaming the file you just processed and with the understanding that I didn't do a deep dive on the code you posted, it appears to be doing pretty much what you want and need to do. Since it's handling one file at a time, it will give you excellent control over any errors that may occur possibly making reruns after an error much easier to do.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply