SQL when moving files with xp_cmdshell, it misses some of them - SQL Server 2005

Instead of reading the .CSV file and calling xp_cmdshell one file at a time, call xp_cmdshell just once like this:

EXEC xp_cmdshell  'for /f "delims=" %F in ("C:\mypath\myfile.csv") do  move "C:\Start%F" "C:\End"'

or better, make a .BAT file that contains similar code and call it with xp_cmdshell.  (In a .BAT file the percent symbols must be doubled:

:myMoveScript.bat
'for /f "delims=" %%F in ("C:\mypath\myfile.csv") do  move "C:\Start%F%" "C:\End"'

----------

EXEC xp_cmdshell 'C:\mypath\myMoveScript.bat'

If necessary, the .BAT file can take the .CSV filename as a parameter.  In general, if all you are doing is reading a .CSV file for a list of filenames and moving them from directoryA to directoryB, using a .BAT script is more efficient and flexible than calling xp_cmdshell for each file name.

(my first script above assumes the filenames in the .CSV file begin with '\' so that it yields C:\Start\filename.ext ... if this is not the case then include the \ in the move command.)

Thanks for the replies!
Sorry I wasnt clear - we actually have to read these in one at a time because the CSV has criterion that need to be analyzed before the file can be moved.  The variables are harvested and lookups are completed before before the SP is called.  

Is there any explanation on why the files would leave the source directory and not complete the full move?  Could I be executing other queries around the SP too quickly?

You should be able to get the results of running the move command in your output, what does that show when you pick through it?

My guess is that there is some sort of SQL time-out occurring here not a problem on the command line in all likely hood.

Can you re-test with just the files which were deleted?

I usually use XP Command shell for reading file data into tables, so I'm certain you should be getting the full output from commands you use back as little temp tables.  You should probably capture that into a log table you can analyze.

It may be possible that the XP Command is NOT picking up Std. Err for some reason but, by default it should pick that up.  Just in case that might be the case, you should amend your statement to include 2&1 in the command which will redirect Std. Err to STd Out, and be picked up by your SQL.

In the attached Code I am going to populate a table based off the output of the Move command you're running and hopefully allow you to better catch what may be happening

Also I would just make sure your data is clean, I always scrub my data via SQL before plugging it into the XP Command, extra slashes or spaces for instance can have unexpected consequences.

However MOVE by itself should NOT be able to delete a file.

  The destination file name is NOT required either, however I put it in just to ensure it is like to like.

--Create Table of DB Names / backup files to Restore
DECLARE @XPCMD_MoveOutputTABLE
( 
	Output sysname null
)

--Declare FileName Handler
DECLARE @filename varchar(255)

--Execute Move, capture output and incert it into the database line by line (The loop is required for this to process properly)
INSERT @XPCMD_MoveOutputexec (' xp_cmdshell ''echo off & for /f "tokens=*" %v in (''''MOVE "C:\Start' + @filename  + '" "C:\End\' + @filename + '" '''') do echo %~nv''')

--Check the contents of that Log of all your moves
Select Output from @XPCMD_MoveOutputexec

Select allOpen in new window