powershell - Azure Runbook 自动化调用嵌套 Runbook。错误 嵌套工作流不支持高级参数验证

Question

我对 Azure Runbook 和自动化还很陌生。

我有几个azure Sql 数据库，并且数据库中有我想按顺序运行的存储过程。在使用 On-premises SQL Server 之前，我们有一个 SQL 作业代理来按顺序运行存储过程。经过一些研究，看起来 SQL 作业代理已被 Azure 自动化取代。

现在我想创建一个 Runbook，它接受参数来逐个运行存储过程。然后创建另一个 Runbook，通过提供运行每个存储过程的参数来调用子 Runbook。

我找到了一个脚本here这使我能够从 Runbook 运行存储过程。

这是 Runbook 脚本:

    workflow SQL_Agent_SprocJob
{
[cmdletbinding()]
param
(
# Fully-qualified name of the Azure DB server
[parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[string] $SqlServerName,

# Name of database to connect and execute against
[parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[string] $DBName,

# Name of stored procedure to be executed
[parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[string] $StoredProcName,

# Credentials for $SqlServerName stored as an Azure Automation credential asset
[parameter(Mandatory=$true)]
[ValidateNotNullOrEmpty()]
[PSCredential] $Credential
)
inlinescript
{
Write-Output “JOB STARTING”

# Setup variables
$ServerName = $Using:SqlServerName
$UserId = $Using:Credential.UserName
$Password = ($Using:Credential).GetNetworkCredential().Password
$DB = $Using:DBName
$SP = $Using:StoredProcName

# Create & Open connection to Database
$DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
$DatabaseConnection.ConnectionString = “Data Source = $ServerName; Initial Catalog = $DB; User ID = $UserId; Password = $Password;”
$DatabaseConnection.Open();
Write-Output “CONNECTION OPENED”

# Create & Define command and query text
$DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
$DatabaseCommand.CommandType = [System.Data.CommandType]::StoredProcedure
$DatabaseCommand.Connection = $DatabaseConnection
$DatabaseCommand.CommandText = $SP

Write-Output “EXECUTING QUERY”
# Execute the query
$DatabaseCommand.ExecuteNonQuery()

# Close connection to DB
$DatabaseConnection.Close()
Write-Output “CONNECTION CLOSED”
Write-Output “JOB COMPLETED”
}

}

然后我想创建另一个 Runbook 并调用子 Runbook“SQL_Agent_SprocJob”来传递参数。

这是我的家长操作手册:

    workflow HelloWorldStoredProcedure
{
     $SqlServerName = "mydbserver.database.windows.net"
     Write-Output $SqlServerName  
     SQL_Agent_SprocJob -SqlServerName $SqlServerName -Credential "myCredentialName" -DBName "myDbName"  -StoredProcName "dbo.HelloWorld"
    Write-Output "Complete!"
}

当我运行该 Runbook 时，该 Runbook 失败并显示以下消息:

Advanced parameter validation is not supported on nested workflows

在此链接中here他们展示了这是运行嵌套 Runbook 的方式:

知道问题出在哪里吗？

Answer 1

ValidateNotNullOrEmpty 是错误所指的高级参数验证。 original example不使用它。

此外，除非您确实需要检查点或并行事件执行，否则请考虑使用普通的 PowerShell Runbook 而不是 PowerShell 工作流:它们更容易编写且启动速度更快。