>Hi All, is it possible to copy or move files from within a sp ?, e.g. if I have a collection of files in \\myserver\myfolder , can I move or copy them to \\myserver\myotherfolder ?

Peter,


To Enable XP_CMDSHELL you will need to run the following:

sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

For COPY/MOVE file functionality, Please find SP and example below:

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

CREATE PROCEDURE [dbo].[usp_CopyFile] 
	@From	VARCHAR(4000),
	@To		VARCHAR(4000),
	@MoveIt BIT = 0
AS
 
DECLARE @CommandBody VARCHAR(4000)
DECLARE @ReturnStatus int
  
IF @MoveIt = 1
	SET @CommandBody ='MOVE /Y "'+ LTRIM(RTRIM(@From)) +'" "'+ LTRIM(RTRIM(@To))+'"'
ELSE
	SET @CommandBody ='COPY /Y "'+ LTRIM(RTRIM(@From)) +'" "'+ LTRIM(RTRIM(@To))+'"'
 
EXEC @ReturnStatus = master.dbo.xp_cmdshell @CommandBody, NO_OUTPUT

RETURN @ReturnStatus
GO

--EXAMPLE --
EXECUTE [dbo].[usp_CopyFile] 
	@From = 'c:\Text1.txt',
	@To	  =	'c:\TEMP',
	@MoveIt = 1

Cheers,

Zoran