5
$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?

Jason Boyd
  • 6,839
  • 4
  • 29
  • 47
MBuchanan
  • 51
  • 1
  • 1
  • 3

2 Answers2

9

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
Community
  • 1
  • 1
dugas
  • 12,025
  • 3
  • 45
  • 51
  • 1
    Aha. I created a datatable and thought this was sufficient for it to behave as such. I am a newbie so I need to revisit on my own but thanks for clarifying this basic idea. – MBuchanan May 06 '16 at 06:59
  • 1
    Thank you dugas. Very simple code change on my part but still need to get my head around the datatable that I was defining and what the difference is. A table is an array one could say. The whole invoke-sqlcmd command lends itself to being a table type within powershell - or at least thats how a dba might assume and go down the wrong path. I have a lot to learn . – MBuchanan May 10 '16 at 12:27
  • 1
    @MBuchanan - you assigned a Datatable to the $CollectionProfile variable, but then you later assigned the return value from Invoke-Sqlcmd to the $CollectionProfile variable. Because of Dynamic Typing, the type of $CollectionProfile changed from a DataTable to whatever is returned from Invoke-Sqlcmd. Hope that helps. – dugas May 10 '16 at 16:11
3

Use one of the column name from select statement in place of Rows, which will give correct result count.

Here in my example I gave name in place of rows which is my first column name in the select statement "Select top 1 * from Sysdatabases". This will give you correct result for top 1 or top 2 ..

 $sql=("select top 1 * FROM sysdatabases")
 $sysdatabases = New-Object System.Data.DataTable
 $sysdatabases = Invoke-Sqlcmd -ServerInstance $Instance -Database $db -Query $sql -ErrorAction Stop  
 $sysdatabases.name.Count
Ramesh Murugesan
  • 601
  • 2
  • 9
  • 16