question

wajika-3218 avatar image
0 Votes"
wajika-3218 asked wajika-3218 commented

How to execute agent job step in powershell?

sql-server-management-objects-smo-programming-guide
smo-object-model-namespaces
using-smo-with-powershell-to-obtain-sql-server-job-information


I want to execute a agent job step through powershell, but I am a newbie for SQL server. I read the document and have some basic understanding, but I still haven't solved the problem.


$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server "BI-DEV-DB"
$jb = $srv.JobServer.Jobs
$jb.Start("LSD_SC") <<< This is modified according to using-smo-with-powershell-to-obtain-sql-server-job-information, but it didn't work

$jp = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Agent.JobStep
$jp.OnSuccessAction(QuitWithSuccess)
$jp.OnSuccessStep(QuitWithSuccess) << I do not understand the role and usage of OnSuccessAction and OnSuccessStep, but I think these two functions are helpful to me

$jr = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Agent.JobServer
$jr.Jobs.Start("LSD_SC") <<< According to my understanding, is there a start event in Microsoft.SqlServer.Management.Smo.Agent.JobServer?

Can anyone give me some help?




sql-server-generalwindows-server-powershellsql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered wajika-3218 commented

why that complicated, use DbaTools CmdLet => Start-DbaAgentJob


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @wajika-3218

in addition:

Get-DbaAgentJobHistory returns all information on the executions still available on each instance(s) of the SQL Server submitted. The cleanup of SQL Agent history determines how many records are kept.

EnumJobHistory(JobHistoryFilter)
The EnumHistory method returns a DataTable object that enumerates the execution history of all jobs.
https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.agent.jobserver.enumjobhistory?redirectedfrom=MSDN&view=sql-smo-160#Microsoft_SqlServer_Management_Smo_Agent_JobServer_EnumJobHistory_Microsoft_SqlServer_Management_Smo_Agent_JobHistoryFilter_

JobHistoryFilter Class
https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.management.smo.agent.jobhistoryfilter?redirectedfrom=MSDN&view=sql-smo-160

Hope this helps with your query!


--If the reply is helpful, please Upvote and Accept as answer--

1 Vote 1 ·
wajika-3218 avatar image wajika-3218 LimitlessTechnology-2700 ·

Thank you for your help and patience

0 Votes 0 ·

Thank you for your answer, I still have a question, how to get the result of the job execution?

0 Votes 0 ·

I found that "Get-DbaAgentJobHistory" can get the execution result.

0 Votes 0 ·