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

robthomas09 used Ask the Experts™
Hello experts,

We're running into quite a strange occurrence here, and hope someone can help.  We have a small script here that is designed to simply pick up .TIF images from one folder, and move them to another folder. Our program reads in a .csv file with the file names to be moved, and then runs:

Currently calling a SP that uses SQL's XP_CMDSHELL function to move files from one static directory to another when given a variable filename:

@filename varchar(255)
EXEC xp_cmdshell 'MOVE "C:\Start' + @filename + '" C:\End'

We found that when the move was complete, we started with 500 files in folder 1, and when we counted folder 2, there were only 420.  All 500 had been removed from folder 1 though.  Not sure how 20% of them had been seemingly erased?


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:

'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.
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

(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?
I'm not sure, but you can still make use of a .BAT file for this, if for no other reason than to log the results:

@echo off
if "%1"=="" exit/b
echo %date% %time% - moving "C:\Start\%~1"  to  "C:\End"  >>C:\End\history.log
move  "C:\Start\%~1"  "C:\End"  1>>C:\End\history.log  2>>C:\End\history.log


EXEC xp_cmdshell 'C:\ScriptPath\myMoveScript.bat "' + @filename + '"'
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer

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
	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

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial