Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Invoke-Sqlcmd Count Expand / Collapse
Posted Monday, January 14, 2013 2:17 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 11:18 AM
Points: 38, Visits: 292

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?


Post #1406945
Posted Wednesday, January 16, 2013 1:07 AM



Group: General Forum Members
Last Login: Thursday, December 25, 2014 8:04 PM
Points: 7,141, Visits: 12,768
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)
foreach($row in $results)

There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1407651
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse