Post reply

BCP Command Error using path that contains space?

  • 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

  • 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" ... '

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

  • December 22, 2006 at 6:55 am

    #679698

    Thanks, that worked!

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

  • 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

  • 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

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

  • 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

  • January 18, 2008 at 6:13 am

    #769604

    thanks a lot

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply