WorkFlow not returning the information expected

Welcome Forums General PowerShell Q&A WorkFlow not returning the information expected

This topic contains 3 replies, has 2 voices, and was last updated by

 
Participant
1 month, 3 weeks ago.

  • Author
    Posts
  • #114076

    Participant
    Points: 123
    Rank: Participant

    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
    
     
    
    
  • #114084

    Participant
    Points: 814
    Helping Hand
    Rank: Major Contributor

    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/

    • #114106

      Participant
      Points: 123
      Rank: Participant

      Thanks for the response.

  • #114109

    Participant
    Points: 123
    Rank: Participant

    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
    
    }
    
    

You must be logged in to reply to this topic.