UNCLE!!! Getting lost in the whole Object, Methods, Properties, and $ stuff. Hoping for help.

Occasional Contributor

PS Newb on 5.1


When last we left our traveler, he had been taught many wonderous things by this community. Indeed, he went on to enjoy many care-free days frolicking amongst the Object, Methods, Properties, and $. He was researching error handling. $error and $PSItem had great info, but were too verbose. So he started delving into the bits and pieces of the info. Upon the discovery of "InvocationInfo" the cry of "Eureka" was heard.


And then it all went badly ...


So here is the snippet of my offending code. Upon inspection of this snippet you will observe that I have introduced the additional column "Bill" in my insert but have not provided a source for Bill. Great! Error ensues and we enter the "catch". In my thinking I should be printing the name of the script, and then printing the error's invocation info. However, as is probably evident to the learned I get something else.




foreach ($Row in $DataTable.Rows) {
    $insert = "insert into grain.dbo.AA_TODDPS (tseq, tnum, tstr, bill)
               Values({0}, {1}, '{2}')" -f $seqnewValue, ($Row.tnum + ($count * 100)), $Row.tstr


        invoke-Sqlcmd -Query $insert -ServerInstance 'WME-VM10-RW'  

   } #end try
        write-output "Name = $PSItem.InvocationInfo.ScriptName"








Name = Invalid column name 'bill'.
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES
 clause must match the number of columns specified in the INSERT statement..InvocationInfo.ScriptName

MyCommand             : Invoke-Sqlcmd
BoundParameters       : {}
UnboundArguments      : {}
ScriptLineNumber      : 48
OffsetInLine          : 9
HistoryId             : 23
ScriptName            : C:\Users\wmeadmin\Documents\PS\TagWork.ps1
Line                  :         invoke-Sqlcmd -Query $insert -ServerInstance 'WME-VM10-RW'  
PositionMessage       : At C:\Users\wmeadmin\Documents\PS\TagWork.ps1:48 char:9
                        +         invoke-Sqlcmd -Query $insert -ServerInstance 'WME-VM10-RW'
                        +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PSScriptRoot          : C:\Users\wmeadmin\Documents\PS
PSCommandPath         : C:\Users\wmeadmin\Documents\PS\TagWork.ps1
InvocationName        : invoke-Sqlcmd





Looking at the documentation it sure looks like $PSItem can get you to InvocationInfo, and that InvocationInfo has a property named "Scriptname". However, no amount banging, pushing, and foul-languageof gets me anything like I want. I get the same thing with ScriptLineNumber, though I swear I had that working at one point.

UPDATE: As I continued to throw stuff at the wall, I realized that


alone give me what I expect. That makes me feel good, but is infuriating at the same time. Evidently there is some nuance (or just my own ignorance) that keeps me from getting what I expect.



2 Replies



Noting your update, it seems you've largely got your head around the original question (though correct me if I'm wrong), so I'll keep this one brief.


Noting your Write-Output on line 13, specifically focusing on the variable reference embedded in the string, that's not going to work as you might intuitively thing it will.


PowerShell's default parsing of variables in strings is pretty basic, and where you think you're accessing the the child variable InvocationInfo, and then its property ScriptName, you're not, as PowerShell will only parse the top-level $PSItem - again, this is by default.


To utilise multi-part references, you need to use the subexpression operator: $(). This is in the same spirit of prefixing strings in C# with the dollar sign where it treats the content inside the operator as code.



So, this:


write-output "Name = $PSItem.InvocationInfo.ScriptName"


Will become this:


Write-Output  -InputObject ("Name = $($PSItem.InvocationInfo.ScriptName)");


Also, on the topic of $PSItem:


  • $PSItem and $_ are interchangeable as they are the same thing: the current object on the pipeline;
  • Because of this, you want to be careful as it can change in a heartbeat.


In order to avoid that easy-to-fall-into trap (where the current object changes), I'd suggest doing any of the following in the catch block:


  • Assign $_ or $PSItem to a local variable, i.e. $MyError = $_; or
  • Refer to the current exception using the native $error array, i.e. $error[0].InvocationInfo.ScriptName, etc.


That way, as you invariably do other things that may result in $_ / $PSItem changing, you're not caught trying to track down not-so-obvious issues like this one.


With what you're doing, tapping into InvocationInfo is the best resource, but purely as an FYI, if you're looking for a quicker way to fetch the script name or path from which it's running, check out the built-in $PSCommandPath (former) and $PSScriptRoot (latter), as these are easier to drop into string for substitution, in commandlets like Join-Path, etc.



As a final note, it's good to see you using throw. I see a lot of people report an error but fail to incorporate any kind of flow control - which you're clearly aware of through using throw.




OK. Let me see if I have this straight:
On a Tuesday, if I am wearing my yellow socks, and you are working from home at your stand-up desk it is Y, if you are sitting, or it is a Thursday and my socks don't match, it is N. However there is the case ...

I am sure it all makes sense when it all makes sense. I once understood the similar contortions that are intrinsic to "C" (I am a "C" guy. None of this new-fangled "C#" stuff).