Stored Procedure to Copy Files



Stored procedure to help you out to copy files see the code bellow

USE [aspnetdb] -- Replace with your DB
GO
/****** Object: StoredProcedure [dbo].[usp_copyfile] Script Date: 04/21/2009 09:22:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[usp_copyfile]
@msg as varchar(200),
@input as varchar(100)
,@retorno as varchar(200) output
as
Begin

declare @drive as varchar(100)
declare @drive_dest as varchar(100)
declare @path as varchar(800)
declare @path_dest as varchar(800)
declare @cmd as varchar(800)
declare @ext as char(3)

set @path_dest='\Dest_Path\'
-- Para Exemplo, não esquecer barra de directorio final
-- Do not forget of blackslash
--
set @path='\PROGRA~1\DATA\'
set @drive='c:'
set @drive_dest='d:'
set @cmd='copy '+@drive+@path+@msg+@ext
set @cmd=@cmd+@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))
+(select cast(month(getdate()) as varchar(2)))
+(select cast(year(getdate()) as varchar(4)))
+replace((select convert(varchar(8),getdate(),108)),':','')+@ext




-- Para Testar Caminhos e Visualizar
-- For testing paths and to show
-- select @cmd

exec master..xp_cmdshell @cmd ,no_output

Set @retorno=@drive_dest+@path_dest+ltrim(rtrim(@input))+(select cast(day(getdate()) as varchar(2)))
+(select cast(month(getdate()) as varchar(2)))
+(select cast(year(getdate()) as varchar(4)))
+replace((select convert(varchar(8),getdate(),108)),':','')+@ext

end