WorkFlow not returning the information expected

PowerShell Help
1

I’m trying to get a WorkFlow that will manage Sql Database Indexes. The intention is to pass a Sql Server Instance name and have the WorkFlow get the databases, then in parallel get the tables in each database, then in parallel get the indexes in each table, and then in parallel check for fragmentation and return the results.

In the WorkFlow I reference other Functions, all of which have been tested and work as expected when used outside of the WorkFlow. Calling the WorkFlow returns nothing and I cant figure out where I’ve gone wrong.

Any guidance, suggestions, greatfully received.

WorkFlow Manage-Indexes

{

[CmdletBinding()]

Param(

[Parameter(Mandatory=$true)]

[ValidateNotNullOrEmpty()]

$SqlServer

)

InlineScript {

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $using:SqlServer

$Dbs = $srv.Databases | ?{$_.Name -Like "csdev*"}

}

foreach -parallel ($Db in $Dbs ) {

$Db

Get-Tables -SqlServer $using:SqlServer -DbID $($Db.ID)

$Tables = Get-Tables -SqlServer $using:SqlServer -DbID $($Db.ID)

foreach -parallel ($tbid in $tables.id) {

$tbid

$Indexes = Get-Indexes -SqlServer $using:SqlServer -DbID $($Db.ID) -TbID $TbID

$Indexes

foreach -parallel ($idx in $Indexes) {

$idx

$Results = Get-Fragmentation -SqlServer $idx.SqlServer -DbID $idx.ID -TbID $idx.TbID -IxID $idx.IxID

$Results

}

}

}

}

 

If I call the Functions in the WorkFlow outside of the WorkFlow I get results expected.

cls

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer

$Dbs = $srv.Databases | ?{$_.Name -Like "csdev*"}

foreach ($Db in $Dbs ) {

$Tables = Get-Tables -SqlServer $SqlServer -DbID $($Db.ID)

foreach($tbid in $tables) {

$Indexes = Get-Indexes -SqlServer $SqlServer -DbID $($Db.ID) -TbID $($TbID.ID)

foreach($idx in $Indexes) {

$Results = Get-Fragmentation -SqlServer $SqlServer -DbID $($Db.ID) -TbID $($TbID.ID) -IxID $($idx.IxID)

$Results

}

}

}

 

 

Returns:

 

DbID : 8

 

TbID : 935010412

 

IxID : 3

 

Fragmentation : 0

 

PageCount : 1

 

 

 

DbID : 8

 

TbID : 935010412

 

IxID : 4

 

Fragmentation : 0

 

PageCount : 1

 

 

 

DbID : 8

 

TbID : 935010412

 

IxID : 1

 

Fragmentation : 50

 

PageCount : 2

 

 

 

DbID : 8

 

TbID : 935010412

 

IxID : 2

 

Fragmentation : 0

 

PageCount : 1

 
2

Workflows cannot see functions declared outside, you cannot treat workflows as functions. Below is a similar thread where Don gave the reason for it briefly. You have to make the functions available inside the workflow.

https://powershell.org/forums/topic/workflow-variable-restrictions/

3

Thanks for the response.

4

i’m trying to work around the issue first encountered but hit something else I can’t figure out.

I’m separating each step out into their own WorkFlow and starting off getting a List of Tables using WorkFlow Get-Tables.

Somehow it appears that parenthesis are being placed round the SqlServer parameter value, so far, I cant figure out why this is happening ?

 

I call the WorkFlow like this:

Foreach($D in $DatabaseList) {

Get-Tables -SqlServer $($D.SqlServer) -DbID $($D.DatabaseID)

}

The error I get is:

Cannot convert the "[SQLUTL-02\DEV]" value of type "Microsoft.SqlServer.Management.Smo.Server" to type

"Microsoft.SqlServer.Management.Smo.Server".

+ CategoryInfo          : InvalidArgument: (:) [], ParentContainsErrorRecordException

+ FullyQualifiedErrorId : ConvertToFinalInvalidCastException

+ PSComputerName        : [localhost]

 

The WorkFlow is this:

WorkFlow Get-Tables

{

Param(

[string]$SqlServer ,

[string]$DbID

)

InlineScript{[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null}

$srv = New-Object Microsoft.SqlServer.Management.Smo.Server $SqlServer

$Tables = $srv.databases.ItemById($DbID).Tables

Return $Tables

}