[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PowerShell Set-StrictMode -Version Latest

Posted on 2014-01-08
3
Medium Priority
?
561 Views
Last Modified: 2014-01-12
I've just discovered "Set-StrictMode -Version Latest" in Powershell and am wondering how to handle this issue, or is it an issue at all?

$MainQuery= Invoke-Sqlcmd -Query $Query

If the SQL returns more than 1 record then $MainQuery.Count is valid, but if there is only 1 result returned the "Count" property is not valid.

I want to show the number of records:
$TotalRecords = $MainQuery.Count
write-host $TotalRecords

*Also note, that in PS3 Microsoft changed something (forget what it is called) where 0 and 1 are different (no longer numbers unless specified with "@()" ) than numbers 2 and higher. As an example "@(1)" has to be used sometimes or it is seen as not a one, but an object or something. Thought I would mention this because it seems like it might be the same issue here?

Anyway, I guess $MainQuery might be  called a dynamic object because the properties change? Hmmm, maybe it is just the change Microsoft made, but I would still like to know if the code is "OK" or if I should handle it differently?

Does PS 4 have a way to test if a property exist for an object and then if it does I could code it so that if .Count was not available then to set the $TotalRecords = 1 else $TotalRecords = $MainQuery.Count

What are your thoughts?
thanks!
0
Comment
Question by:johnj_01201
3 Comments
 
LVL 41

Accepted Solution

by:
footech earned 2000 total points
ID: 39766242
If a query or cmdlet returns multiple objects, saving this to a variable is generally (I hestitate to say "always") an array, and each object is an element of the array.  When only one object is returned, it's generally not an array.  That explains why the .count property is not always available.  You can get around this by forcing the results to be stored in an array, even if there's only one object/element.  Typically you can do this by enclosing the command(s) which return the results within array notation.  So try the below.
$MainQuery= @(Invoke-Sqlcmd -Query $Query)

Open in new window

0
 
LVL 71

Expert Comment

by:Qlemo
ID: 39766737
I'm not aware of a change of handling of 0 and 1 differently in PS3. Probably you mean the handling of (no element), (one element) and (many elements). No element is always something tricky, because it can mean many things, and sometimes needs to be ignored, in other cases not, and such.

As footech elaborated, enclosing results in @(...) will make sure you can treat them as "array" all the time.
0
 
LVL 1

Author Closing Comment

by:johnj_01201
ID: 39775516
Thanks. I'm new to PS and get confused. Your answer is the same (I think) to what I've found a while ago when trying to get IF statements to work when 1 result was returned from invoke-sql.  Based on your explanation, your fix is exactly what I need to do whenever I use invoke-sqlcmd and expect the .count property.

http://connect.microsoft.com/SQLServer/feedback/details/796367/count-property-of-invoke-sqlcmd-result-is-null-when-record-set-contains-exactly-one-record
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This script can help you clean up your user profile database by comparing profiles to Active Directory users in a particular OU, and removing the profiles that don't match.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Screencast - Getting to Know the Pipeline

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question