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

**URL:** <https://forums.powershell.org/t/invoke-sqlcmd-no-data-when-there-is-only-one-row/17391>\
**Category:** PowerShell Help\
**Created:** [August 23, 2021, 6:46pm UTC](https://forums.powershell.org/t/invoke-sqlcmd-no-data-when-there-is-only-one-row/17391 "2021-08-23T18:46:13Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![mg2017](https://avatars.discourse-cdn.com/v4/letter/m/96bed5/32.png) [@mg2017](https://forums.powershell.org/u/mg2017)\
**Post date:** [August 23, 2021, 6:46pm UTC](https://forums.powershell.org/t/invoke-sqlcmd-no-data-when-there-is-only-one-row/17391/1 "2021-08-23T18:46:13Z")

</div>

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.  
Thx  
MG

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [August 23, 2021, 7:17pm UTC](https://forums.powershell.org/t/invoke-sqlcmd-no-data-when-there-is-only-one-row/17391/2 "2021-08-23T19:17:31Z")

</div>

Please format the code as code. 👆🏽

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:

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

```

---

<div class="post-metadata">

**Author:** ![mg2017](https://avatars.discourse-cdn.com/v4/letter/m/96bed5/32.png) [@mg2017](https://forums.powershell.org/u/mg2017)\
**Post date:** [August 23, 2021, 8:18pm UTC](https://forums.powershell.org/t/invoke-sqlcmd-no-data-when-there-is-only-one-row/17391/3 "2021-08-23T20:18:47Z")

</div>

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.  
Thanks  
MG

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [August 23, 2021, 8:39pm UTC](https://forums.powershell.org/t/invoke-sqlcmd-no-data-when-there-is-only-one-row/17391/4 "2021-08-23T20:39:21Z")

</div>

Great you found another solution as well. 👍🏽

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.

---

<div class="post-metadata">

**Author:** ![mg2017](https://avatars.discourse-cdn.com/v4/letter/m/96bed5/32.png) [@mg2017](https://forums.powershell.org/u/mg2017)\
**Post date:** [August 24, 2021, 11:48am UTC](https://forums.powershell.org/t/invoke-sqlcmd-no-data-when-there-is-only-one-row/17391/5 "2021-08-24T11:48:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:21pm UTC](https://forums.powershell.org/t/invoke-sqlcmd-no-data-when-there-is-only-one-row/17391/6 "2024-05-16T20:21:44Z")

</div>