Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: BCP fun and games

    The following code works
    Code:
    DECLARE @file varchar(255)
        SET @file = 'C:\test.csv'
    DECLARE @cmd varchar(8000)
        SET @cmd = 'BCP playdb.dbo.test_import IN "' + @file + '" -t, -r\n -c -T -S ' + @@ServerName
    EXEC master..xp_cmdshell @cmd
    However, this does not
    Code:
    DECLARE @file varchar(255)
        SET @file = 'C:\Documents and Settings\gvee\Desktop\test.csv'
    DECLARE @cmd varchar(8000)
        SET @cmd = 'BCP playdb.dbo.test_import IN "' + @file + '" -t, -r\n -c -T -S ' + @@ServerName
    EXEC master..xp_cmdshell @cmd
    With the following error message
    SQLState = HY000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unable to open BCP host data-file
    The only difference is the filepath, what could be the issue?

    I'm running this on a local SQL instance using SQL Authentication with all the files residing on the same machine, therefore I'm pretty sure it aint permissions related!

    If you need any more info from me to help me solve this please let me know
    George
    Home | Blog

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    wrap it with double quotes
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Thanks for the idea, but it already is - see the SET @cmd line
    George
    Home | Blog

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    To expand a smidgeon on Brett's comment, when you use xp_cmdshell instead of BULK INSERT you have to play by the rules of the command line instead of the rules of the GUI. Command line tools require that file or path names with spaces in them be enclosed in quotation marks, otherwise the command line parser gets crabby.

    -PatP

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ohh you guys

    P.S. the full path works with BULK INSERT but obviously I can't use a variable for the field (unless I wrap bulk insert as dynamic SQL )
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    or you can go with Docum~1, which I do not recommend.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Come again?
    As I understand it, windows limits file paths to 255 characters, which is respected by BCP (can dig out where I read this if necessary, probably BoL), so I don't think the length is a problem.

    Interestingly these both worK:
    Code:
    SET @file = 'C:\test\test.csv'
    SET @file = 'C:\test 2\test.csv'
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by gvee
    Come again?
    DOS shorthand for Documents & Settings.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Go find the file using Windows Exploder, then double check the path. Once you are sure of the path (I'd recommend using Cut-and-Paste) then I'd try to do a directory of it using xp_cmdshell to see what you get.

    -PatP

  10. #10
    Join Date
    Jul 2003
    Posts
    4
    Is this a permissions issue? Can BCP access that folder? Try sending it to the "All Users" folder.

  11. #11
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    it would be Docume~1, not Docum~1.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hey chaps, thanks for all the responses so far.
    I haven't forgotten about this, just haven't had a chance to look at this particular issue again - will feedback when I can
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •