Post reply

bcp to csv syntax errors

  • December 3, 2009 at 4:38 pm

    #213156

    Hello -

    Please help! I am trying to use bcp in a procedure to create a csv file. However I keep getting the following syntax error: Incorrect syntax near 'SELECT * FROM menus.dbo.ri_res WHERE facility_no = '123''.

    Here is the simplified code that I am using just trying to get rid of the syntax errors: EXEC master..xp_cmdshell bcp "SELECT * FROM menus.dbo.ri_res WHERE facility_no = '123'" queryout "s:\menus\menu12320091203094911.ri_res.csv" -U SA -n -t| -T -k

    Any help would be greatly appreciated.

  • December 3, 2009 at 5:04 pm

    #1087436

    As per BOL syntax for xp_cmshell is

    xp_cmdshell { 'command_string' } [ , no_output ]

    Try putting your bcp command in quotes like this:

    EXEC master..xp_cmdshell 'bcp "SELECT * FROM menus.dbo.ri_res WHERE facility_no = ''123''" queryout "s:\menus\menu12320091203094911.ri_res.csv" -U SA -n -t| -T -k'

    Let me know if its not working out for you.

    Thanks,

    Supriya

  • December 3, 2009 at 5:12 pm

    #1087437

    It seems to have worked - rows affected, etc. However, no file was created.

  • December 3, 2009 at 5:19 pm

    #1087439

    Not sure if I replied incorrectly or not, I am new to this. I will try replying to you as well.

    It seems to have worked - rows affected, etc. However, no file was created.

  • December 3, 2009 at 5:37 pm

    #1087441

    Supriya -

    I also noticed that besides no file being created there were only 12 rows were affected when I know there are 86 rows that should have been pulled by the select.

  • December 4, 2009 at 9:53 am

    #1087861

    What is the output you are getting when you run this in SSMS? I am thinking 12 rows affected is 12 line of errors not the number of rows from the table. Also, have you checked the bcp syntax? Its been a while since I used bcp so have to look it up in BOL too. 🙂

    Actually, when I ran the statement it's giving me error: "-T' is not recognized as an internal or external command,operable program or batch file.NULL"

    That maybe because it doesn't like the pipe(-t|) before -T. try replacing it with "-t\t" for a tab just to check if that would create a file for you.

    -Supriya

Viewing 6 posts - 1 through 5 (of 5 total)

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