Trying to get New-UDSQLTable running

PowerShell Universal
1

I am trying out the new UDSQL component after watching @adam’s youtube video. Thinking it will save me a lot of load time, as many of my dashboards for a new customer are querying their PeopleSoft database. Unfortunately, I am running into an error:

Cannot process command because of one or more missing mandatory parameters: Query CountQuery SqlInstance Database Columns.

Problem is, I have all of the parameters present. Below is my code:

New-UDDashboard -Title "SQL" -Content {
   $AESKeyFilePath = "<path to AESKey>"
   $SecurePwdFilePath = "<path to encrypted PW>"
   $userUPN = "<service account name>"
   $AESKey = Get-Content -Path $AESKeyFilePath 
   $pwdTxt = Get-Content -Path $SecurePwdFilePath
   $securePass = $pwdTxt | ConvertTo-SecureString -Key $AESKey

   $aParams = @{
      Title = "Active Department IDs"
       Columns = @("DEPTID", "DEPT_DESCR", "COMPANY")
       Query = "select DISTINCT DEPTID, DEPT_DESCR, COMPANY from PS_WR_POSN_CACHE"
       CountQuery = "SELECT COUNT(*) as Count FROM PS_WR_POSN_CACHE"
       Credential = $creds
       SqlInstance = "SQL Server"
       Database = "MainDB"
   }

   New-UDSQLTable @$aParams
}

I know the connection to the sql server works from the server hosting PSU, and this works just fine:

   New-UDDashboard -Title "SQL" -Content {
      $AESKeyFilePath = "<path to AESKey>"
      $SecurePwdFilePath = "<path to encrypted PW>"
      $userUPN = "<service account name>"
      $AESKey = Get-Content -Path $AESKeyFilePath 
      $pwdTxt = Get-Content -Path $SecurePwdFilePath
      $securePass = $pwdTxt | ConvertTo-SecureString -Key $AESKey
      $sqlServer = "SQL Server"
      $dBase = "MainDB"
      $query = "select DISTINCT DEPTID, DEPT_DESCR, COMPANY from PS_WR_POSN_CACHE"
      $aQuery = Invoke-Sqlcmd -ServerInstance $sqlServer `
                        -Database $dBase `
                        -Query $query `
                        -Credential $creds `
                        -EncryptConnection: $false

      New-UDList -Id "myList" -Content {
         $aQuery | 
            Sort DEPT_DESCR |
                % {
                    New-UDListItem -Label "$($_.DEPT_DESCR)"
                    }
      }
}

Any ideas what I could be doing wrong, that it seems not to recognize (at least) one of the parameters, or even any thoughts on narrowing down which one it doesn’t like? I also checked the log file, but do not see any errors.

Product: PowerShell Universal
Version: 1.5.16
2

When splatting, you should remove the $

Does this yield a different result?

   New-UDSQLTable @aParams
3

Jeez it’s a Monday. Sorry about that, totally missed it.

1 Like