Azure Runbook Automation Calling nested Runbook. error Advanced parameter validation is not supported on nested workflows

Question

I am quite new to azure runbooks and automation.

I have several azure Sql databases and there are stored procedures in the databases that I would like to run in a order. Before in On premises SQL Server we had a SQL Job Agent to run the stored procedures in order. Doing some research it looks like SQL Job agent are replaced with Azure Automation.

Now I would like to create a runbook that would accept parameters to run stored procedures one by one. Then create another runbook to call child runbook by providing parameters to run each stored procedures.

I found a script here that allows me to run stored procedures from a Runbook.

here is the runbook script:

    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”
}

}

Then I would like to create another runbook and call the child runbook which is "SQL_Agent_SprocJob" to pass parameters in.

Here is my parent runbook:

    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!"
}

When I run this the runbook the runbook fails with the message:

Advanced parameter validation is not supported on nested workflows

In this link here they are showing this is the way to run nested runbooks:

Any idea where is the issue?

Answer 1

ValidateNotNullOrEmpty is the advanced parameter validation the error is referring to. The original example does not use that.

Also, unless you really need checkpoints or parallel activity execution, consider using plain PowerShell runbooks instead of PowerShell Workflow: they are much easier to author and start faster.

Answer 2

There is really not much over head between a regular script and a work flow. I have always used workflows with automation.

Please take a look at this blog article from "hey, scripting guy!" on tech net. He wrote a great series of articles on PowerShell for newbies.

As for your script, I try to keep things simple. I write often for MS SQL TIPS and did an article on compression within a Azure SQL database.

How does that relate to your question?

Inside that article are two functions that I have used often when coding PowerShell solutions.

The first one applies directly to your question on how to execute dynamic TSQL. Since you are controlling the input, you do not have to worry about injection.

The second one returns a data table object from a SELECT statement. This could be used as input to your processing.

Several examples of using these functions are shown in that blog.

Happy coding.

John

The Crafty DBA

Name: Exec-NonQuery-SqlDb Purpose: Execute a DELETE, INSERT, UPDATE or DDL statement.

[CmdletBinding()] 

param(
    [Parameter(Mandatory = $true)]
    [String] $ConnStr,

    [Parameter(Mandatory = $true)]
    [string] $SqlQry
)

Name: Get-DataSet-SqlDb()
Purpose: Retrieve data from SELECT query.

[CmdletBinding()] 

param(
    [Parameter(Mandatory = $true)]
    [String] $ConnStr,

    [Parameter(Mandatory = $true)]
    [string] $SqlQry,

    [Parameter(Mandatory=$false)] 
    [ValidateSet("DataSet", "DataTable", "DataRow")] 
    [string]$As="DataRow" 
)