none
BCP problem

    Question

  • This BCP sentence works perfect when I execute it manually( on the cmd window from the OS)  but fails when I execute it as a job step in a job.

    This is the sentence:

    bcp "SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list') FROM [mydatabase].[dbo].[DeadlockEvents] ORDER BY RecordId DESC" queryout "deadlock.xdl"

     

    -SMyServer\Mydbinstance -T -c -q

    And this is the error:

    Message
    Executed as user: Domain\_sqlagentaccount . Copy direction must be either 'in' or 'out'.  Syntax Error in 'queryout'.  usage: bcp [[db_name.]owner.]table_name[:slice_num] [partition pname] {in | out} [filename]   [-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] [-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] [-Y]   [--maxconn maximum_connections] [--show-fi] [--hide-vcc].  Process Exit Code -1.  The step failed.

    Do you know if the syntax should be changed when I execute this from a job step?
    Wednesday, March 3, 2010 6:02 PM

Answers

  • yes Adam all was in one line but when I copied here split in 2 lines.

    However,  I found the problem:

    Looks like when I run the bcp command from the cmd window it calls to the correct bcp.exe file, but not when I execute it from the SQL Server Job, so I had to change the command to:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" "SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list') FROM [mydatabase].[dbo].[DeadlockEvents] ORDER BY RecordId DESC" queryout "e:\prod\deadlock.xdl" -SMyserver\Myinstance -T -c -q

    Now is working without errors.
    • Marked as answer by LuisGranados Wednesday, March 3, 2010 9:37 PM
    Wednesday, March 3, 2010 9:25 PM

All replies

  • You are putting the entire command on a single line.... right?


    http://jahaines.blogspot.com/
    Wednesday, March 3, 2010 8:07 PM
    Moderator
  • yes Adam all was in one line but when I copied here split in 2 lines.

    However,  I found the problem:

    Looks like when I run the bcp command from the cmd window it calls to the correct bcp.exe file, but not when I execute it from the SQL Server Job, so I had to change the command to:

    "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\bcp.exe" "SELECT TOP 1 [DeadlockGraph].query('/TextData/deadlock-list') FROM [mydatabase].[dbo].[DeadlockEvents] ORDER BY RecordId DESC" queryout "e:\prod\deadlock.xdl" -SMyserver\Myinstance -T -c -q

    Now is working without errors.
    • Marked as answer by LuisGranados Wednesday, March 3, 2010 9:37 PM
    Wednesday, March 3, 2010 9:25 PM