Run SQL Server Job from Command Line

Question

Post reply

Run SQL Server Job from Command Line

Bob Bridges-173967

SSCrazy

Points: 2094
More actions
March 21, 2007 at 12:16 pm

#68094

Good afternoon,
I need to daisy chain a SQL Server job with an AS400 job (have the AS400 job kick off the SQL Server job). I've been told that this can be done but how do I do this? What connectivity do I need and more pressing right now, what is the command line syntax for such a job?
thanks,
Bob

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adam Angelini SSCrazy Eights Points: 8548 More actions · Answer 1

use either osql or sqlcmd, depending on your version of SQL (2000 uses osql, 2005 is sqlcmd)

you can use that to run sp_startjob

so your command may look like this:

osql -E -SserverName -q"sp_start_job @job_name = 'job_name', @server_name = 'server_name'"

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 2

Thanks Adam... I had to tweak it just a bit but it works. One further question... when I run this from a command prompt, after it starts, there is a message that the command "started successfully." Then the next line looks like this...

1>

If I press the enter key it simply adds a >2 below that.

Does this mean the system is waiting on something to get me back to a regular command prompt?

Thanks again,

Bob

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 3

Adam, I figured it out... in your example change the small q to a captial Q

Thanks again, Bob

Bob Bridges-173967 SSCrazy Points: 2094 More actions · Answer 4

I got this to work using the sa account but not when I run it with a SQL Server account. I gave rights to the SQL Server account to execute the stored procedure as well as the sp_start_job stored procedure.

Any ideas how I can get a generic SQL Server account to work?

Thanks,

Bob

vconlan SSC-Addicted Points: 497 More actions · Answer 5

I am trying to use the same command and I am having trouble getting it to work. I am running this:

RUNRMTCMD CMD('sqlcmd -U user -P password -S server name -Q "msdb.dbo.sp _start_job @job_name = ''FACT852: ProcessRetailSales'', @server_name = '' server''"') RMTLOCNAME('99.99.99.99' *IP) RMTUSER(user) RMTPWD(password)