# Step 1 - Need to start SQL Server Agent, then run the script
below to see the jobs
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
gci
# Step 2 - If you need to run all of the jobs
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
gci |
%{$_.start()}
# Step 3- If you need to run a particular job. You need the
category ID
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
get-item syspolicy_purge_history.
# Step 4 - If you need to find all of the categories IDs for
all Jobs
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
gci |
ft name, categoryID, -autosize
# Step 5 - A couple of ways to run a particualr job
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
(get-item syspolicy_purge_history.0).start()
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
$a=get-item
syspolicy_purge_history.0
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
$a.start()
# Step 6 - You can also run a particular job step in a job
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
$a.start("theNameOfAName")
# Step 7 - You can run jobs in a remote server. In the script
below, Xu-Acer-PC pretends to a remote server, although it is the same as
localhost in reality.
# The rest is the same as the localhost.
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
cd\
PS SQLSERVER:\>
cd sql\xu-acer-pc\default\jobserver\jobs
PS SQLSERVER:\sql\xu-acer-pc\default\jobserver\jobs>
gci
# Step 8 - how about to call the same job across multiple
servers?
# First, you need to find the categoryID for the job using
T-SQL or PS. Let's assume we get 100 for the CategoryID for the common job
called ETLLoads
# T-SQL: Use MSDB GO Select * from syscategories
# PS way - see above
# Let's try this way.Assuming we have two servers: localhost
xu-acer-pc
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
$a =
"localhost", "xu-acer-pc"
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>$a | % {cd \; cd sql\S_\default\jobserver\jobs; "Starting: $_"; (get-item ETLLoads.100).start();
"Done: $_";}
# The script above is likely to generate an error if the job
on the 2nd server has a different categoryID. How can we change the categoryID
dynamically?
# Step 9 - Workaround 1.
create a ps1 file for each server manually.
cd \;
cd sql\localhost\default\jobserver\jobs; #hard-code the server name for each server
"Starting: $_";
(get-item ETLLoads.100).start(); #hard-code the
categoryID for the job on each server
"Done: $_";
cd \;
cd sql\server2\default\jobserver\jobs;
"Starting: $_";
(get-item ETLLoads.101).start();
"Done: $_";
# Step 9 - Workaround 2.
Use sp_start_job in msdb and the $a variable
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>
$a =
"localhost", "xu-acer-pc"
PS SQLSERVER:\sql\localhost\default\jobserver\jobs>$a | % {cd \; cd sql\$_\default\databases\msdb; "Starting: $_"; invoke-sqlcmd
"sp_start_job 'ETLLoads'"; "Done: $_";}
