Can't Create Agent Job from Command Line

  • I created a job to run daily under SQL Server Agent. It uses an executable on the file system, within which a

    connection to a database is made, but when creating the job in the Management Studio no database needed to be

    specified. The job runs correctly.

    Within the Management Studio I auto-created a script for the job. I can delete the job, run the script within

    the Management studio, and the job is re-created correctly. However, I cannot succesfully re-create the job

    from a command line.

    Command I'm using: sqlcmd -E -i myscript.sql -o errorfile.txt

    The first time I run, I get:

    Changed database context to 'msdb'

    SQLServerAgent is not currently running so it cannot be notified of this action.

    When I rerun with the same command, no changes to the script, I get:

    Changed database context to 'msdb'

    Msg 14261, level 16, State 1, Server xxxxxxx, procedure sp_verify_job, Line 46

    The specified @name ('nameIgaveToJob') already exists.

    If I edit the script and give the job a different name, this behavior repeats - the first time I run it I

    get the message that the agent isn't running, the 2nd time I get the message the name already exists.

    I don't have privileges on the server, but the administrator tells me he has checked and the agent is running.

    The job scheduled with the agent does run daily.

    Thank you for any insights...

  • You are trying to create the job on a remote server not your local server.

    When you fire the below command, it tries to connect to you local instance of SQL server

    sqlcmd -E -i myscript.sql -o errorfile.txt

    You need to specify the server name in this command to exeute the script on a remote server:

    sqlcmd -E -S[Servername\Instancename] -i myscript.sql -o errorfile.txt

    -Vikas Bindra

  • Thank you - that was it.

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

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