November 1, 2018 at 4:55 am
Hello,
I have a procedure that copy files from server to another server.
Source - \\ServerSource\u
Destination - Z:\output\
All the relevant files at the source server located at folder U.
Now i have a files at sub-folders (for example U:\USA , U:\Argentina , U:\Italy ETC...).
The script don't drill down and take the files at the sub-folders (the script attached below)
Please assist me
EXEC [CopyFilesFromSourceToDest] @sourcepath = ' \\ServerSource\u' , @destpath = 'Z:\output\' , @Execute = 1
CREATE proc [dbo].[CopyFilesFromSourceToDest]
@sourcepath varchar(1000) , @destpath varchar(1000) , @Execute bit = 0
as
begin
exec sp_configure 'show advanced options' , 1
reconfigure
exec sp_configure 'xp_cmdshell' , 1
reconfigure
IF OBJECT_ID('tempdb..#SourceFiles') IS NOT NULL
DROP TABLE #SourceFiles;
CREATE TABLE #SourceFiles (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);
INSERT #SourceFiles (subdirectory,depth,isfile)
EXEC xp_dirtree @sourcepath, 1, 1
delete from #SourceFiles
where subdirectory not like '%.PDF'
IF OBJECT_ID('tempdb..#DestFiles') IS NOT NULL
DROP TABLE #DestFiles;
CREATE TABLE #DestFiles (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);
INSERT #DestFiles (subdirectory,depth,isfile)
EXEC xp_dirtree @destpath, 0,1
select 'xcopy ' + @sourcepath + '\"' + #SourceFiles.subdirectory + '" ' + @destpath + ' /D /A' command
into #fileslist
from #SourceFiles
where subdirectory not in (select subdirectory from #DestFiles )
and subdirectory like '%.PDF'
if @Execute = 0
select * from #fileslist
else
Begin
declare c cursor for (select * from #fileslist )
declare @command varchar(1000)
open c
fetch next from c into @command
while (@@FETCH_STATUS = 0 )
begin
set @command = 'xp_cmdshell ''' + @command + ''''
print (@command)
exec (@command)
fetch next from c into @command
end
close c
deallocate c
End
exec sp_configure 'show advanced options' , 0
reconfigure
end
GO
November 1, 2018 at 7:47 am
See below the solution i found
EXEC [CopyFilesFromSourceToDest] @sourcepath = ' \\ServerSource\u' , @destpath = 'Z:\output\' , @Execute = 1
Create procedure [dbo].[CopyFilesFromSourceToDest]
@sourcepath varchar(1000) , @destpath varchar(1000) , @Execute bit = 0
as
begin
exec sp_configure 'show advanced options' , 1
reconfigure
exec sp_configure 'xp_cmdshell' , 1
reconfigure
declare @command1 varchar (1000)
set @command1 = 'DIR '+@sourcepath + ' /s /b'
IF OBJECT_ID('tempdb..#SourceFiles') IS NOT NULL
DROP TABLE #SourceFiles;
CREATE TABLE #SourceFiles
(
FullPath NVARCHAR(max),
sourcepath AS SUBSTRING(FullPath, 1, LEN(FullPath)-CHARINDEX('\',REVERSE(FullPath))+1) PERSISTED,
subdirectory AS RIGHT(FullPath,CHARINDEX('\',REVERSE(FullPath))-1) PERSISTED
)
;
INSERT INTO #SourceFiles
EXEC xp_CmdShell @command1
IF OBJECT_ID('tempdb..#DestFiles') IS NOT NULL
DROP TABLE #DestFiles;
CREATE TABLE #DestFiles (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);
INSERT #DestFiles (subdirectory,depth,isfile)
EXEC xp_dirtree @destpath, 0,1
select 'xcopy ' + #SourceFiles.sourcepath + '"' + #SourceFiles.subdirectory + '" ' + @destpath + ' /D /A' command
into #fileslist
from #SourceFiles
where subdirectory not in (select subdirectory from #DestFiles )
and subdirectory like '%.PDF'
if @Execute = 0
select * from #fileslist
else
Begin
declare c cursor for (select * from #fileslist ) declare @command varchar(max)
open c
fetch next from c into @command
while (@@FETCH_STATUS = 0 )
begin
set @command = 'xp_cmdshell ''' + @command + ''''
print (@command)
exec (@command)
fetch next from c into @command
end
close c
deallocate c
End
exec sp_configure 'show advanced options' , 0
reconfigure
end
November 6, 2018 at 8:13 am
just use robocopy in xp_cmdshell, it will skip files that are already there and copy only newer files
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply