Invoke-DbaQuery

PowerShell Universal
1 
Product: PowerShell Universal
Version: 1.5.14

I’m using Invoke-DbaQuery in a dashboard, this works ok. I can get results in the dashboard and via a PowerShell session on the same server.

However, if i add the parameter -As 'psobject' (so like: Invoke-DbaQuery -As 'psobject'...) I don’t get any results in the dashboard - but - if I run the same line in a PowerShell session on the server, I do get results.

The dahsboard log shows [Invoke-DbaQuery] [srv-uatnsmsql01] Failed during execution | Unable to find type [DBNullScrubber].

As soon as I change the -As parameter from the default “DataRow” to anything else (like psobject or dataset etc) it doesn’t seem to work when executed from a dashboard.

[ValidateSet("DataSet", "DataTable", "DataRow", "PSObject", "PSObjectArray", "SingleValue")]
        [string]$As = "DataRow",
2

I found a similar issue referenced here: PowerShell 7: CREATE table works, SELECT table doesnt · Issue #28 · RamblingCookieMonster/PSSQLite · GitHub

It might give you a hint as to what is going on.

1 Like
3

Update:

Got around this by not using the -As parameter and working with the returned DataRow slightly differently…

BEFORE (Invoke-DbaQuery wasn’t returning anything)

[array]$session:UDColumns = @()
(Invoke-DbaQuery -As 'psobject' -SqlCredential $cache:cfgSQLcreds -SqlInstance $cache:cfgSQLserver -Database $cache:cfgSQLdatabase -Query "SELECT top 1 * FROM ( select route_id,route_desc from route ) AS subquery;")[0].psobject.Properties | foreach-object {
    [array]$session:UDColumns += New-UDTableColumn -Property $_.Name -IncludeInSearch -ShowFilter -FilterType text
}

AFTER (works ok with -As parameter removed)

[array]$session:UDColumns = @()
$columnNamesFromQuery = (Invoke-DbaQuery -SqlCredential $cache:cfgSQLcreds -SqlInstance $cache:cfgSQLserver -Database $cache:cfgSQLdatabase -Query "SELECT top 1 * FROM ( select route_id,route_desc from route ) AS subquery;" -ReadOnly).Table.Columns | Select-Object -ExpandProperty ColumnName
    if($null -ne $columnNamesFromQuery) {
     foreach($colName in $columnNamesFromQuery) {
         [array]$session:UDColumns += New-UDTableColumn -Property $colName -IncludeInExport -IncludeInSearch -ShowFilter -FilterType text
     }
}

could make it simpler, but left it more verbose for readability

1 Like
4

Just mentioning here that the same issue came up when using the PSSQLite module and a SQLite instance. Running local PowerShell session tests is fine, returning psobjects is ok - but as soon as I run the same code within a PSU dashboard, I get null results and see the DBNULLSCRUBBER error.

The fix for me was explicitly setting the -As parameter to datarow for the Invoke-SqliteQuery cmdlet to actually return data.

didn’t work within a dashboard:
$Count = Invoke-SqliteQuery -DataSource “X:\Path\test.sqlitedb” -Query "SELECT COUNT() FROM $cache:cfgTableName" -As ‘psobject’ | Select-Object -ExpandProperty 'Count()’

$Count = Invoke-SqliteQuery -DataSource “X:\Path\test.sqlitedb” -Query "SELECT COUNT() FROM $cache:cfgTableName" | Select-Object -ExpandProperty 'Count()’

updated to work within a dashboard:
$Count = Invoke-SqliteQuery -DataSource “X:\Path\test.sqlitedb” -Query "SELECT COUNT() FROM $cache:cfgTableName" -As ‘datarow’ | Select-Object -ExpandProperty 'Count()’

Cheers,
Steve.