Powershell 运行 SQL 作业，延迟，然后运行下一个 [英] Powershell run SQL job, delay, then run the next one

问题描述

我正在编写一个 Powershell 脚本，该脚本对本地 SQL Server 数据库执行多项操作.

我正在做的一件事是运行多个 SQL 作业，一个接一个.我像这样运行它们:

 sqlcmd -S .\ -Q "EXECUTE msdb.dbo.sp_start_job @job_name = '重建内容资产关系数据'"

有没有办法让 Powershell 延迟运行下一个作业，直到第一个作业完成?

谢谢

解决方案

要从 PowerShell 访问 SQL 代理作业，您可以使用 SMO:

编辑:考虑效率，如果您要将此功能添加到您的脚本中，我会取出 SMO 加载并将其放在脚本顶部附近(在此之前功能).如果每次调用该函数时它都会重新加载程序集，它可能会减慢您的脚本速度.

<预><代码>函数 Get-SQLJobStatus{参数 ([string]$server, [string]$JobName)# 加载 SMO 程序集，如果我们运行的是 SQL 2008 DLL，则加载 SMOExtended 和 SQLWMIManagement 库[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') |出空# 创建对象连接到 SQL 实例$srv = 新对象Microsoft.SqlServer.Management.Smo.Server"$server# 用于允许多个作业名称的管道运行如果($工作名称){foreach($j 在 $jobName){$srv.JobServer.Jobs |其中 {$_.Name -match $JobName} |选择名称，当前运行状态}}else #显示实例的所有作业{$srv.JobServer.Jobs |选择名称，当前运行状态# 结束获取 SQLJobStatus}

您可以使用此功能的方法示例:

<预><代码>#将显示实例上的所有作业获取 SQLJobStatus MyServer#pipe 多于一项工作以获取状态"myJob","myJob2" |foreach {Get-SQLJobStatus -Server MyServer -JobName $_}#获取一份工作的状态Get-SQLJobStatus -Server MyServer -JobName "MyJob"

您可以在脚本中使用此函数，然后在 while 循环中反复调用它，直到您的作业状态显示为空闲".至少在我看来这是我认为可行的:)

I am writing a Powershell script that does several things with a local SQL Server database.

One thing I am doing is running several SQL jobs, one after another. I run them like this:

 sqlcmd -S .\ -Q "EXECUTE msdb.dbo.sp_start_job @job_name = 'Rebuild Content Asset Relationship Data'"

Is there a way to get Powershell to delay running the next job until the first one is completed?

Thanks

解决方案

To get access to SQL Agent Jobs from PowerShell you can use SMO:

EDIT: Thinking on efficiency if you are going to add this function to your script I would take the SMO loading out and just place it near the top of your script (prior to this function). It will probably slow your script down if every time you call the function it reloads the assembly.

Function Get-SQLJobStatus
{
    param ([string]$server, [string]$JobName)
    # Load SMO assembly, and if we're running SQL 2008 DLLs load the SMOExtended and SQLWMIManagement libraries
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

    # Create object to connect to SQL Instance
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $server

    # used to allow piping of more than one job name to function
    if($JobName)
    {
        foreach($j in $jobName)
        {
            $srv.JobServer.Jobs | where {$_.Name -match $JobName} | Select Name, CurrentRunStatus
        }
    }
    else #display all jobs for the instance
    {
        $srv.JobServer.Jobs | Select Name, CurrentRunStatus
} #end of Get-SQLJobStatus
}

Example of ways you could use this function:

#will display all jobs on the instance
Get-SQLJobStatus MyServer

#pipe in more than one job to get status
"myJob","myJob2" | foreach {Get-SQLJobStatus -Server MyServer -JobName $_}

#get status of one job
Get-SQLJobStatus -Server MyServer -JobName "MyJob"

You could utilize this function in your script and just repeatedly call it in a while loop or something until your job status shows "Idle". At least in my head that is what I think could work :)

这篇关于Powershell 运行 SQL 作业，延迟，然后运行下一个的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！