Problem with BCP command

I am trying to execute following command using xp_cmdshell

EXEC master..xp_cmdshell 'bcp "select nc_value from fileade.dbo.nms_command (nolock) order by nc_pk desc" queryout \\filw\ExternalTools\NMS3\reports\NMS3_20070525_051507.txt -c -S"FILW2K" -Uabc -Pabc'

but the error message returned is

Copy direction must be either 'in' or 'out'.

Syntax Error in 'queryout'.

usage: bcp [[database_name.]owner.]table_name[lice_number] {in | out} datafile

[-m maxerrors] [-f formatfile] [-e errfile]

[-F firstrow] [-L lastrow] [-b batchsize]

[-n] [-c] [-t field_terminator] [-r row_terminator]

[-U username] [-P password] [-I interfaces_file] [-S server]

[-a display_charset] [-q datafile_charset] [-z language] [-v]

[-A packet size] [-J client character set]

[-T text or image size] [-E] [-g id_start_value] [-N] [-X]

[-M LabelName LabelValue] [-labeled]

[-K keytab_file] [-R remote_server_principal]

[-V [security_options]] [-Z security_mechanism] [-Q]

NULL

Further the same command is running successfully in my DEV environment.

That sounds like you have a SQL 7 bcp.exe in your path. Run "bcp -v" on both machines and make sure the versions match.

|||Wrong forum. Moving to Transact-SQL.|||

When the path for the output file contains spaces or other 'non-acceptable' characters, you 'should' enclose it in double quotes.

The Server name does not need to be in double quotes.

|||

Hi Tom,

I have checked the version on all the three plateform

DEV : 8.00.382

PROD : 8.00.382

TestPROD : 8.00.382

and version are same.

|||

Hi Arnie,

The command is giving problem on Production environment only while in DEV & staging server working perfectly wheather to export file in local drive or in a network drive.

|||On the production server, does the SQL Agent account have permissions for the file locations?|||

Not Sure how to check this,

BUt we have checkred that other scheduled JOBS which also export some file from PROD DB server to other server are working OK, Further the BCP command is executed via windows service

Windows Service

Batch File

Stored Procedure

BCP Command.

|||Somewhere you have an old bcp.exe which is being picked up. The error message you posted above is from the SQL 7 bcp.exe program, not 8.00.382. The "queryout" option was added in SQL 2000 bcp.exe.

Search your hard drive for bcp.exe and remove anything not in C:\Program Files\Microsoft SQL Server\90 (or 80)\tools\binn.

|||

Hi Tom,

Your check point really help me to found the the problem although Production Hard Drive was not having the bcp.exe of version 7 instead the server was having Sybase BCP.EXE also , so whenever the window service try to execute the BCP command instead of picking up the SQL Server BCP path it was picking the Syabse BCP due to which the error was coming.

Thanks again for your help.

|||Good. I am glad you found it.

sql