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

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

So i have to create a DTS package to run my job and then use dtsrun to run that package?

Sorry I sent you the wrong script. I'll be back with the correct one shortly. Mark

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

Cheers Mark.

can we pass a parameter to this stored procedure when running from a command line?

how then is the syntax?

thanks.

Sorry LimaCharlie, after six years I doubt that the thread has much life left in it!

-PatP

Oh it does, just used it for info.

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:
-E means use use Integrated (Windows) security. If you need to use a SQL username and password, you can do this instead:
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

Yes

This massage is too short

osql is deprecated in 2005 and beyond. use sqlcmd instead.