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[
[-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.