Sql-server – SQL Agent powershell context reference

powershellsql serversql-server-2005sql-server-2008

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-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 satisfactory resolution. Research indicated the PowerShell listed under SQL Agent was SQLPS and starting an instance of it by right clicking on the Agent automagically dropped me into the correct location. It was only when I pasted my interactive code into the job step that I learned of the difference as previously mentioned.

Job type of OS put me into an identical state in that I could not find a way to determine which instance dropped me into the command shell. Sure, I could sqlcmd and get the value of @@servername but if I knew what connection to start sqlcmd, I wouldn't need to to query the database 😉

TSQL could probably work if we enable xp_cmdshell but I'm not sure if they have it turned on—government facility and they can be persnickety on non-default settings. Even then, I'm stuck futzing with dynamic SQL and losing a lot of the expressiveness and power that PowerShell lends.

While a bit ungainly, I thought defining a variable at the first step and passing that to successor steps but research turned up this article Handling Multiple Job Steps (BOL)

Job steps must be self-contained. That is, a job cannot pass Boolean values, data, or numeric values between job steps. You can, however, pass values from one Transact-SQL job step to another by using permanent tables or global temporary tables. You can pass values from job steps that run executable programs from one job step to another job step by using files.

I can't use common tricks like a well-known file/environment variables/registry setting that Foo.exe looks for since that would prevent concurrent execution across instances.

TL;DR:

In a SQL Agent Job step of type PowerShell, how can you determine the instance of SQL Server that launched the process?

Best Answer

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://docs.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.

Related Solutions

Sql-server – SQL Agent embedded PowerShell script in CmdExec step fails with import-module sqlps

Since you are using SQL Server 2008 R2 you can simply create your SQL Agent Job with the step configured as a "PowerShell" type, instead of trying to use the CmdExec. You only need to include the "meat" of your script. Since you are using the PowerShell type of a SQL Agent step it has already called the powershell.exe and imported the SQLPS module for you. So an example: enter image description here

With your code I believe you can simply configure the step as shown below: enter image description here

Sql-server – SQL Agent – PowerShell step “syntax error”

This is a not very intuitive and I was never able to find anything concrete on the explanation [e.g. no exact BOL or white paper was found].

The syntax error in the SQL Agent job is a T-SQL syntax error based on User Tokens. So that basically means that a PowerShell Sub-Expression Operator is treated as a Token to SQL Server Agent. So in PowerShell this $( ) appears to be treated as reserved character sequence for SQL Server Agent. So SQL Agent would be looking for something like this T-SQL example from the BOL article referenced: PRINT N'Current database name is $(A-DBN)' ; .

So in my script when it reached ,@DriveLetter = '$($c.DriveLetter)', the "$c.DriveLetter" is not one of the tokens allowed.

The workaround to this is simply to not use sub-expression statements in PowerShell. I would make the adjustments in my script so this:


$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
    @ServerName = '$($c.ServerName)'
    ,@DriveLetter = '$($c.DriveLetter)'
    ,@DiskSpaceCapacityGB = $($c.DiskSpaceCapacityGB)
    ,@DiskFreeSpaceGB = $($c.DiskFreeSpaceGB)
"@

would have to be modified to something like this:


$severName = $c.ServerName
$driveLetter = $c.DriveLetter
$capacityGB = $c.DiskSpaceCapacityGB
$freeGB = $c.DiskFreeSpaceGB

$qAddServerDiskSpace = @"
EXEC [dbo].[StoredProcInsert]
   @ServerName = '$serverName'
   ,@DriveLetter = '$driveLetter'
   ,@DiskSpaceCapacityGB = $CapacityGB
   ,@DiskFreeSpaceGB = $freeGB
"@

Made the above adjustments to my script and it runs perfectly now.