Powershell Invoke-Sqlcmd to DataTable rowcount is incorrect when its 1 record

Question

$sql=("select top 1 * FROM CollectionProfile")

$CollectionProfile = New-Object System.Data.DataTable

$CollectionProfile = Invoke-Sqlcmd -ServerInstance $Instance -Database $db -Query $sql -ErrorAction Stop  

$CollectionProfile.Rows.Count

RETURNS :0

But if I change the TOP count to 2 -

$sql=("select top 2 * FROM CollectionProfile")

RETURNS :2

Driving me crazy and yes, I could not find a single reference to this on the "innernets". I must be doing something wrong, but WHAT?

Answer 1

When you use the query with TOP 1, Invoke-SqlCmd returns a DataRow. When you use the query with TOP 2, Invoke-SqlCmd returns an Array of DataRows. Invoke-SqlCmd does not return a DataTable. You could change your code to force an array to be returned (see here: force array), and then check the Count on it:

$sql = ("select top 1 * FROM CollectionProfile")

$CollectionProfile = @(Invoke-Sqlcmd -ServerInstance $Instance -Database $db -Query $sql -ErrorAction Stop)

$CollectionProfile.Count #Returns 0 with Null, 1 with TOP 1, and 2 with TOP 2