0

I'm trying export csv (and other files) from numerous subfolders. These are archive folders that are being dropping dropped on our database server to be loaded. In any case I found two lines of code that work (or did work) but when loaded into a bat file or called by xp_cmdshell commands do not work.

These commands are intended to extract all the files located in multiple (hundreds) of sub directories. Again these work when I put them in the command prompt manually but not when called via bat(or cmd file) or xp_cmdshell from SQL-Server.

for /f "tokens=*" %a in ('dir /b /s /a-d "C:\SPSData\UTDB1_Stage1"') do @move "%a" "C:\SPSData\UTDB1_Stage1"

MOVE C:\SPSData\UTDB1_Stage1\ *\ * C:\SPSData\UTDB1_Stage1\
Ryan
  • 1
  • 1
    "*dropping dropped*"? "*works in CMD but not in [...] CMD*"? You may want to reconsider that title. – Clifford Jun 08 '16 at 20:27
  • Strictly it is not an MS-DOS command - it is a *Windows shell command*; it does not rely on the MS-DOS subsystem or `command.com` to run, and in Win64 there is no MS-DOS sub-system in any case. `cmd.exe` is a true Windows executable and is not related to MS-DOS except in the sense that it shares a number of commands and command syntax. – Clifford Jun 08 '16 at 20:37

1 Answers1

2

When used in a batch file, you need to escape the % variable introducer by prefixing with another % - substitute %a with %%a:

for /f "tokens=*" %%a in ('dir /b /s /a-d "C:\SPSData\UTDB1_Stage1"') do @move "%%a" "C:\SPSData\UTDB1_Stage1"

I would imagine that invocation via xp_cmdshell requires the same.

Clifford
  • 88,407
  • 13
  • 85
  • 165
  • Thanks man, I'm embarrassed to admit how many hours I spent on this. Truly you made my day/ week. – Ryan Jun 10 '16 at 19:00
  • @Ryan : You are welcome - it is pretty arcane knowledge familiar only perhaps to those who cut their teeth on MS-DOS. – Clifford Jun 10 '16 at 19:34