Results 1 to 12 of 12
  1. #1
    Join Date
    Jul 2003
    Posts
    16

    Unanswered: Running agent jobs from command line

    Hi,

    How can i run a SQL Server Agent Job from the command line on the same box? I am using SQL Server 2000.

    Thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    Check out the SQL Books Online for Command Line execution of SQL Agent

    The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS package can be stored in the Microsoft® SQL Server™ msdb database, a COM-structured storage file, or SQL Server Meta Data Services.

    Syntax
    dtsrun
    [/?] |
    [
    [
    /[~]S server_name[\instance_name]
    { {/[~]U user_name [/[~]P password]} | /E }
    ]
    {
    {/[~]N package_name }
    | {/[~]G package_guid_string}
    | {/[~]V package_version_guid_string}
    }
    [/[~]M package_password]
    [/[~]F filename]
    [/[~]R repository_database_name]
    [/A global_variable_name:typeid=value]
    [/L log_file_name]
    [/W NT_event_log_completion_status]
    [/Z] [/!X] [/!D] [/!Y] [/!C]
    ]



    dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password

  3. #3
    Join Date
    Jul 2003
    Posts
    16
    So i have to create a DTS package to run my job and then use dtsrun to run that package?

  4. #4
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    Sorry I sent you the wrong script. I'll be back with the correct one shortly. Mark

  5. #5
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    You could create a stored procedure to run the job. Example:
    (You may be able to pass the name through as a parameter ??)

    CREATE PROCEDURE sp_RunJob
    AS

    BEGIN

    EXEC msdb..sp_start_job @job_name = 'The job name'

    END

    You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax.

    Then from the command line, create a .bat file and put the following isql command synax into the .bat file:


    isql /U username -n -E /d "database name" -Q "stored procedure name" /S servername -oC:\returncode.txt

    This will actually produce some output into the returncode.txt file, which might be useful. I'm not sure whether you'll have to return a value back out of the stored procedure or not. I've not tried. My sample above it a cut down version. But anyhow there should be enough above to give you the idea in terms of syntax etc.

    You would then just execute the .bat file on your server/pc etc, perhaps via a scheduler etc...


    Anyhow a search on SQL Books Online will give you all the details. Search for isql , isqlw etc..

  6. #6
    Join Date
    Jul 2003
    Posts
    16
    Cheers Mark.

  7. #7
    Join Date
    Oct 2005
    Posts
    119
    Quote Originally Posted by mtracey
    You could create a stored procedure to run the job. Example:
    (You may be able to pass the name through as a parameter ??)

    CREATE PROCEDURE sp_RunJob
    AS

    BEGIN

    EXEC msdb..sp_start_job @job_name = 'The job name'

    END

    You can also use the job id etc... do a search in the Books Online for sp_start_job and you'll get the syntax.

    Then from the command line, create a .bat file and put the following isql command synax into the .bat file:


    isql /U username -n -E /d "database name" -Q "stored procedure name" /S servername -oC:\returncode.txt

    This will actually produce some output into the returncode.txt file, which might be useful. I'm not sure whether you'll have to return a value back out of the stored procedure or not. I've not tried. My sample above it a cut down version. But anyhow there should be enough above to give you the idea in terms of syntax etc.

    You would then just execute the .bat file on your server/pc etc, perhaps via a scheduler etc...


    Anyhow a search on SQL Books Online will give you all the details. Search for isql , isqlw etc..
    can we pass a parameter to this stored procedure when running from a command line?

    how then is the syntax?

    thanks.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Sorry LimaCharlie, after six years I doubt that the thread has much life left in it!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    Oh it does, just used it for info.

  10. #10
    Join Date
    Dec 2009
    Posts
    1

    Update

    I thought I'd just update this thread with the latest info as came up number 1 in Google and didn't quite answer the question for me.

    You can run a SQL server agent job from the command line with the following:
    Code:
    osql -E -d MSDB -Q "sp_start_job 'MySQLJob'"
    -E means use use Integrated (Windows) security. If you need to use a SQL username and password, you can do this instead:
    Code:
    osql -U user1 -P password -d MSDB -Q "sp_start_job 'MySQLJob'"
    Either of these statements can be placed inside a batch file for convenience but it's important to note that if you use a % character in your command line then it needs to be escaped to %% when used from inside the batch file.

    The following links may also be useful:
    sp_start_job docs (note the required permissions)
    osql Utility

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by LimaCharlie View Post
    can we pass a parameter to this stored procedure when running from a command line?

    how then is the syntax?

    thanks.
    Yes

    This massage is too short
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    osql is deprecated in 2005 and beyond. use sqlcmd instead.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •