none
BCP Query returns NULL result

    Question

  • I have a single client server (first of 43) who have something misconfigured apparently that prevents simple bcp query to execute. The query doesn't return errors as I have seen when security is an issue, the query result when executed from SSMS 2008 R2 (note server also has 2005 instance installed for different vendor) I only get a single row result of NULL.

    declare @path varchar(8000), @bcpcommand varchar(8000)

     

    set @path = 'c:\temp\dor_cuv.csv'

    set @bcpcommand = ' bcp "select reasoncode, reason from mcduffie13.dbo.reason" queryout "c:\temp\dor_cuv.txt" -T -c -t "," -S "atthomas-pc\sqlexpress" '

    exec master..xp_cmdshell @bcpcommand 

    Interestingly, if I provide BCP with an absolute path to the folder location of the bcp.exe tool, the command executes perfectly. Ideas? Suggestions?



    Tracy Thomas Georgia, USA

    Saturday, June 14, 2014 1:53 PM

Answers

  • Found it! I removed the bcp.exe from the 90/tools/binn folder and now we are in great shape. Now, I've got to contact the vendor for the 2008 MSSQL app to see if they use BCP and make certain I've not broken them. :)

    Tracy Thomas Georgia, USA

    • Marked as answer by Tracy Thomas Monday, June 16, 2014 4:19 PM
    Monday, June 16, 2014 4:18 PM

All replies

  • That may be because you've multiple instances of sql server as well as bcp installed in your machine. I used to have this issue and if you run it in command prompt you'll see error message due to version conflict. In my case also passing full path works fine.

    Can you check value for PATH environment variable and make sure correct version path appears first?


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    Saturday, June 14, 2014 3:45 PM
  • Thanks for the feedback - however BCP from the command prompt works flawlessly without any versioning issues. Still hoping for an easy configuration fix on the local server rather than reengineering the application or switching to a new local server at the client shop. TIA for any additional advice.

    Tracy Thomas Georgia, USA

    Saturday, June 14, 2014 7:15 PM
  • It certainly sounds like a funky setting of the PATH variable is pulling your legs. The fact that it works when you open a command-line window, proves little, as your environment variables may not be the same as for the service account for SQL Server.

    It does not even have to be PATH - it could just be that there is a BCP.EXE or BCP.BAT in the default directory for xp_cmdshell. Try:

    xp_cmdshell 'DIR BCP.*'


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Saturday, June 14, 2014 8:02 PM
  •  Volume in drive C is OS
     Volume Serial Number is CAFC-BC46
    NULL
     Directory of C:\Windows\system32
    NULL
    File Not Found
    NULL

    Tracy Thomas Georgia, USA

    Monday, June 16, 2014 12:28 PM
  • Well, it was worth a try. You will need to investigate PATH then.

    Run

    xp_cmdshell 'ECHO %PATH%'

    And check each component for a BCP which should not be there.  Beware the output may fold over more than one line in the output. Ignore the line break.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Monday, June 16, 2014 2:29 PM
  • Found it! I removed the bcp.exe from the 90/tools/binn folder and now we are in great shape. Now, I've got to contact the vendor for the 2008 MSSQL app to see if they use BCP and make certain I've not broken them. :)

    Tracy Thomas Georgia, USA

    • Marked as answer by Tracy Thomas Monday, June 16, 2014 4:19 PM
    Monday, June 16, 2014 4:18 PM