Invoke-Sqlcmd Count

• Eric Zierdt

  Ten Centuries

  Points: 1333

  More actions

  January 14, 2013 at 2:17 pm

  #269200

  Hello,

  I searched for this on here and google and didn't see an exact answer to my question, so sorry if it's been asked and answered before. If I run a query using Invoke-Sqlcmd and put it in a variable:

  $results = Invoke-Sqlcmd -Query $query -ServerInstance $QueryServer -Database $QueryDB;

  and I get multiple rows back, I see that $results has a type of System.Array. I can get a count of the number of returned results by using $results.Length.

  However if I get only 1 row back, the Type is Syste.Data.DataRow and $results.Length is NULL

  I've gotten around this by creating a new variable called ResultsLen and doing the following:

  $resultsLen = $results.<field in query>.Length

  $resultsLen += $results.Length

  but this doens't really work, as the $results.<field in query>.Length is a count of the number of characters in the returned field. this works for what I need...which is just to run a IF statement to only run some code if there are 1+ records returned in the query...but I'm wondering if there is a better way of doing it?

  Thanks.

• Orlando Colamatteo

  SSC Guru

  Points: 182279

  More actions

  January 16, 2013 at 1:07 am

  #1577675

  I agree, it's a pain. It's something they fixed in PowerShell 3.0.

  I just drop right into my loop if I get a result and it handles it, but I do have to check for $null first:

  Import-Module “sqlps” -DisableNameChecking

  # 1 row

  #$results = Invoke-Sqlcmd -Query "select top 1 * from sys.tables where 1=1" -ServerInstance ".\SQL2012" -Database "master";

  # 2 rows

  #$results = Invoke-Sqlcmd -Query "select top 2 * from sys.tables where 1=1" -ServerInstance ".\SQL2012" -Database "master";

  # 0 rows

  $results = Invoke-Sqlcmd -Query "select * from sys.tables where 1=0" -ServerInstance ".\SQL2012" -Database "master";

  if($results -ne $null)

  {

  $results.GetType()

  $results.Length

  foreach($row in $results)

  {

  $row[0]

  }

  }

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• July 5, 2023 at 9:41 pm

  This was removed by the editor as SPAM