BCP Command Error using path that contains space?

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  December 21, 2006 at 3:31 pm

  #115406

  Can anyone tell me how to specify a filepath or filename with a space in a BCP command?

  Thanks in Advance!!

  **Error Message:Unknown argument 'images.bat' on command line.

  **Command:BCP "select 'copy \\chevrolet\data\SALES\ImagesStckNbr\'+ replace(stocknumber,CHAR(34),'')+'_*.JPG \\chevrolet\data\SALES\ImagesOld\'+replace(stocknumber,CHAR(34),'')+'_*.JPG ' from CIF.dbo.InventoryVehicles i where datediff(dd,solddate,getdate()) between 7 and 14 and displayonweb=1 and i.storeid=2" queryout D:\temp\modify images.bat -c

  **This is successful without the space

  **Command:BCP "select 'copy \\chevrolet\data\SALES\ImagesStckNbr\'+ replace(stocknumber,CHAR(34),'')+'_*.JPG \\chevrolet\data\SALES\ImagesOld\'+replace(stocknumber,CHAR(34),'')+'_*.JPG ' from CIF.dbo.InventoryVehicles i where datediff(dd,solddate,getdate()) between 7 and 14 and displayonweb=1 and i.storeid=2" queryout D:\temp\modifyimages.bat -c

• john-132685

  SSCertifiable

  Points: 6134

  More actions

  December 21, 2006 at 4:22 pm

  #679606

  in dos command line you can wrap paths with double quotes

  select 'copy "\\ really long path or whatever.txt" ... '

• Ronald San Juan

  SSCrazy

  Points: 2937

  More actions

  December 21, 2006 at 4:30 pm

  #679610

  Indeed, use the double quotes.

  Merry Christmas!    ß Click Here

  ---

  Ronald San Juan | SQL DBA
  ID 710124 ~ Code "Northwind"

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  December 22, 2006 at 6:55 am

  #679698

  Thanks, that worked!

• Ted Horwitz

  SSC Veteran

  Points: 237

  More actions

  January 16, 2007 at 1:36 pm

  #683602

  I've been wondering about this for a long time. I've tried to do commands like this:

  bcp dbname.dbo.tablename out C:\Documents and Settings\impact1.dlm -Sserver -Uuser -t\t

  and they will not work. I've tried single quotes, double quotes, different positions, etc. It complains about the 'and'.

  Anyone have any ideas?

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  January 16, 2007 at 1:50 pm

  #683608

  Hi Ted,

  I had the same problem.  This code below works for me (a small sample from my production code)....

  -- Use BCP to output the view

  declare @storeid int set @storeid = 1

  declare @drive varchar(10)

  if @storeid = 1 set @drive = 'C:'

  declare @nfilename varchar(255)

  set @nfilename = '"'+@drive+'\Program Files\Interflow\Internet\NEW'

  DECLARE @Cmd varchar(500)

  SET @Cmd='BCP "selectstocknbr,yr,mk,mdl from inventory.dbo.vwAutoTrader i where i.storeid='+cast(@storeid as varchar)+' and newused=''NEW'' " queryout '+@nfilename +' -T -c -t,'

  EXEC master.dbo.xp_cmdshell @Cmd

  Hope that helps!  Kim

• Ted Horwitz

  SSC Veteran

  Points: 237

  More actions

  January 16, 2007 at 2:56 pm

  #683626

  Thanks so much for responding.

  Hmm, master.dbo.xp_cmdshell .... wonder if that's something I'm allowed to do?

  Well, I don't know what I've been doing wrong for months, but it worked like a charm today! I just put double quotes around the whole file path, like I thought I had been doing. This is my first day back after being out sick for a couple weeks, so maybe my head's just cleared and the brain is functioning.

  Sorry to bother you,

  Ted

• Kbrown

  SSCommitted

  Points: 1794

  More actions

  January 17, 2007 at 7:40 am

  #683724

  Hi Ted,

  Glad to hear you got things working!

  Just wanted to post a note about xp_cmdshell.  You can find some good info by looking at Books On Line (BOL) about the command.  Basically, it allows you to issue a dos command.  I use it some administrative scripts to copy files from one directory or another or to ftp files.  Using xp_cmdshell allows me to do it right from a stored procedure in SQL Server.

  Kim

  From BOL:

  xp_cmdshell

  Executes a given command string as an operating-system command shell and returns any output as rows of text. Grants nonadministrative users permissions to execute xp_cmdshell.

• Ted Horwitz

  SSC Veteran

  Points: 237

  More actions

  January 17, 2007 at 7:56 am

  #683734

  Cool, Kim, thanks so much, I will look into it as soon as I get a chance. Believe it or not, I am tied up with a rush project modifying a bunch of mainframe stuff right now.

  Ted

• srinivasdoodam

  SSC Journeyman

  Points: 83

  More actions

  January 18, 2008 at 6:13 am

  #769604

  thanks a lot