Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2002
    Posts
    4

    Unhappy Unanswered: bcp errors with queryout

    I am having problems running bcp with queryout. I believe some of this stems from the fact that I also have the Sybase tools installed on the database servers and it in turn added its binn directory for the Sybase bcp.exe to the environment variables. Seeing this then, I would like to resolve this issue with a solution that does not rely on the order of environment variables.

    Here is what is happening:

    1) If I just try to run bcp on my server I get...

    exec master.dbo.xp_cmdshell 'bcp ?'
    /*
    output --------------------------------------------------------------
    'bcp' is not recognized as an internal or external command,
    operable program or batch file.
    NULL
    */

    2) ...so, I add the full path to the location of the bcp program and this command works:

    exec master.dbo.xp_cmdshell '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" ?'
    /*
    output ------------------------------------------------------------------------------------usage: C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} datafile
    [-m maxerrors] [-f formatfile] [-e errfile]
    [-F firstrow] [-L lastrow] [-b batchsize]
    [-n native type] [-c character type] [-w wide character type]
    [-N keep non-text native] [-V file format version] [-q quoted identifier]
    [-C code page specifier] [-t field terminator] [-r row terminator]
    [-i inputfile] [-o outfile] [-a packetsize]
    [-S server name] [-U username] [-P password]
    [-T trusted connection] [-v version] [-R regional enable]
    [-k keep null values] [-E keep identity values]
    [-h "load hints"]
    NULL
    */

    3) I then attempt a bcp out of a Northwin table... and this works as well

    DECLARE @cmdline VARCHAR(4000)
    SELECT @cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" Northwind.dbo.Categories out C:\test.txt -c -T'
    EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
    /*
    output
    -----------------
    NULL
    Starting copy...
    NULL
    8 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.): total 31
    NULL
    */

    4) I then attempt a queryout and it fails, indicating that it no longer understand the long filename

    DECLARE @cmdline VARCHAR(4000)
    SELECT @cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" queryout C:\test.txt -c -T'
    EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
    /*
    output ------------------------------------------------------------------
    'C:\Program' is not recognized as an internal or external command,
    operable program or batch file.
    NULL
    */

  2. #2
    Join Date
    Oct 2002
    Posts
    4

    more information

    been doing some more testing on my own local SQL Server. It seems that even if the environment variables are all fine, if I fully qualify the bcp exe and include a query (with double-quotes) it fails. So, what I am wondering is, can one not fully qualify the bcp.exe and do a query at the same time?:

    --the below works
    DECLARE @cmdline VARCHAR(4000)
    SELECT @cmdline = 'bcp "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
    EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT

    --...while this does NOT work!
    DECLARE @cmdline VARCHAR(4000)
    SELECT @cmdline = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
    EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT
    /*
    output --------
    'C:\Program' is not recognized as an internal or external command,
    operable program or batch file.
    NULL
    */

  3. #3
    Join Date
    Apr 2004
    Posts
    22

    Re: more information

    try to

    SELECT @cmdline = 'C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe "SELECT CategoryID FROM Northwind.dbo.Categories" QUERYOUT C:\test.txt -c -T'
    EXEC master..xp_cmdshell @cmdline--, NO_OUTPUT

  4. #4
    Join Date
    Apr 2004
    Posts
    22

    Unhappy Re: more information

    sorry, really not works

Posting Permissions

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