Avatar of robthomas09

asked on 

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

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?


Microsoft DOSMicrosoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Ben Personick (Previously QCubed)
Avatar of knightEknight
Flag of United States of America image

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"'
Avatar of knightEknight
Flag of United States of America image

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'
Avatar of knightEknight
Flag of United States of America image

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.
Avatar of knightEknight
Flag of United States of America image

(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.)
Avatar of robthomas09


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?
Avatar of knightEknight
Flag of United States of America image

Blurred text
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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

Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews


IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo