Invoke-Sqlcmd Count

  • 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.

  • 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

  • This was removed by the editor as SPAM

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply