Invoke-SQLCmd - No data when there is only one row

Fellow Powershellers, I have run into something that leaves me speechless and confused.
I am a DBA and have been writing SQL queries and sps for over 25 years. What I have come across is what seems to be how invoke-sqlcmd is handling the output.
I have a simple sp that returns rows. Works perfectly. Then I hit a condition where only ONE row is returned. invoke-sqlcmd would return NOTHING. So, I took the sql and did a Select TOP 2 … in the query and got results. So, when only one row is returned, invoke-sqlcmd is not populating the output object.
I put my query in the $IN_SQLQuery variable.

$results = Invoke-Sqlcmd -ServerInstance $MainSQLServerNameConnection -Database $Maindbname -Username $IN_SQLUserName -Password $IN_SQLPassword -Query $IN_SQLQuery -QueryTimeout 600 -AbortOnError

The counts from $results is NOTHING when only one row comes out of the sql. It does not matter whether it is from a sp or t-sql directly.

I really would like to get this to work - conveting over to ado is going to take more time than I want to give it.

Please format the code as code. :point_up_2:t4:

I do not have experiences with SQL but if it is what I think it is this is a common behaviour of PowerShell. It returns a result just not as an array. When a query returns more than one element PowerShell creates an array implicitly. When there’s only one element PowerShell returns it as it is. So in all cases I remember the solution was to cast the result to an array.
Try this:

$results = @(
    Invoke-Sqlcmd -ServerInstance $MainSQLServerNameConnection -Database $Maindbname -Username $IN_SQLUserName -Password $IN_SQLPassword -Query $IN_SQLQuery -QueryTimeout 600 -AbortOnError

Hi Olaf,
Many thanks. Yes, this works with less code changes on my side. From my understanding the invoke-sqlcmd, like i am using it, returns a .net datatable/datarow object. I also found another way that worked as well. It was using the -AS Datatables option. As shown here. $results = Invoke-Sqlcmd -ServerInstance $MainSQLServerNameConnection -Database $Maindbname -Username $IN_SQLUserName -Password $IN_SQLPassword -Query $IN_SQLQuery -As DataTables -QueryTimeout 600 -AbortOnError

The -AS datatable approach requires me to do a bit more changes to make it work in my Excel processing - not many - but a bit more. Regardless, it worked as well. I will likley stay with your Array appraoch.

1 Like

Great you found another solution as well. :+1:t4:

Is it realy that hard to format your code as code? It is that simple: Place your cursor on an empty line, click the preformatted text button ( </> ) and paste your code … that’s all. Please.


HI Olaf, thx. Sorry about that. will do better going forward.