CODEWITHSUNDEEP
sql-serverpowershell
MBuchanan
MBuchanan

Reputation: 61

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

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

Upvotes: 6

Views: 11717

Answers (2)

Ramesh Murugesan
Ramesh Murugesan

Reputation: 731

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

Upvotes: 4

dugas
dugas

Reputation: 12453

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

Upvotes: 10

Related Questions