SQL Agent powershell context reference

At my new job, we have multiple named instances on each servers. e.g.

• Server1\Dev

• Server1\DevIntegrated

• Server1\QA

I have a SQL PowerShell script in the works that calls out to the OS, invokes Foo.exe but needs to pass a command line parameter (the connection string). A SQL Agent job will exist on each instance, with a step of type PowerShell, that needs to know what the current context is. i.e. This execution began on DevIntegrated.

I have no desire to have every script start with...

$thisInstance = "Dev"

...especially since I'd have to edit that when we migrate to environments (new servers and named instances) in the upcoming months.

If I start SQLPS, I can determine my instance by slicing and dicing the results of Get-Location or running

(Invoke-Sqlcmd -Query "SELECT @@servername AS ServerName" -SuppressProviderContextWarning).ServerName

When the SQL Agent starts a job of type PowerShell, it starts in the C:\windows\system32 and the Get-Location route doesn't work as it's not in the SQLSERVER context. I can change into that context but I'll be at the "root" of SQL Server and won't know what instance I should be in. Using the Invoke-Sqlcmd route won't work either for same reason (technically, it times out as there is no default instance)

To the best of my knowledge, I've enumerated all the basic "things" I can get into the job log but nothing seems to show SQLSERVER:\SQL\Server1\DevIntegrated

• Get-ChildItem

• Get-Host

• Get-Location

• Get-Process

• Get-PSDrive

• Get-PSProvider

• Get-Service

• Get-TraceSource

• Get-Variable

Get-Process seems like I could use that and some voodoo of trying to cobble things together by hitting the instances and matching spids but that just sounds like a bloody-hack from hell. There must be something basic that I'm missing, can anyone shed some light?

Alternatives to PowerShell investigated

I had investigated using other job types and didn't get a satisfa

If you look in SQL Server BOL, SQL Server Agent provides a set of "tokens" that it will substitute into both the job step command text and the output file (the later will prevent the GUI "view" button from working). These tokens seem to work for any type of step except T-SQL.

https://learn.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps#sql-server-agent-tokens

So, if you have a SQL 2008 PowerShell step, you can start it with:

$sqlInstance = "$(ESCAPE_DQUOTE(SRVR))"

You may need to use MACH (machine name) and INST (just instance name) instead, because with the default instance SRVR == MACH, but with named instances SRVR == MACH\INST.

StackExchange Database Administrators Q#3484, answer score: 10