Post reply

Sybase bcp.exe runs when executing MSSQL xp_cmdshell bcp.

  • October 6, 2005 at 2:30 pm

    #102846

    Hi,

    I'm working in a multi database environment.

    When a bcp is run for sql server, I need to either be in the C:\Program Files\Microsoft SQL Server\80\Tools\Binn directory or execute the bcp with the path in it, like this...

    "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "Select col1, col2 from table1" queryout "\\server\path\filename.txt" -S server -U user -P password -c /t "|"

    This works fine from a command prompt but when I use this code....

    declare @bcp varchar(1000)

    select @bcp = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe" "Select col1, col2 from table1" queryout "\\server\path\filename.txt" -S server -U user -P password -c /t "|"'

    exec master.dbo.xp_cmdshell @bcp

    ...with xp_cmdshell I get a "The syntax of the command is incorrect." message.

    Any ideas what's up with this?

    Thanks for any suggestions...

  • October 6, 2005 at 2:37 pm

    #595668

    I'd start by printing the @bcp variable after setting it, copying it, pasting it into the command tool, and running it from there, if you haven't already done so.  Maybe what @bcp is being set to isn't what you expect.

  • October 6, 2005 at 2:48 pm

    #595679

    master.dbo.xp_cmdshell can take only one string in double quotes. You are trying to pass 3.

    You don't need quotes for filename.txt and you can replace long string for bcp.exe with call of "sqlbcp.bat" having this "C:\....\bcp.exe" inside

    _____________
    Code for TallyGenerator

  • October 7, 2005 at 9:26 am

    #595907

    the bat file idea's good, although I'm having some trouble passing the parameters to it. After %9 I've read that I need to do some shifting to get all the parameters but haven't figured that out yet.

    Thanks for the ideas

Viewing 4 posts - 1 through 3 (of 3 total)

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