pass the instance name to an agent job

I want a SQL Agent job to find the server\instance name which launched it.

I have a bunch of SQL servers - VMs, active-active 2 node clusters, active-passive 2 node clusters, SQL Server 2008 R2. I want to run the BPA on them on a schedule.

I want to run this as a SQL Agent job on each instance. I want to use MSX to distribute the job to the instances. I have the same proxy everywhere to use as the runas credential.

So, I have a Powershell script which will run the BPA (doesn't work as a SQL Agent Powershell jobstep because it uses Import-Module). This is stored in a ps1 file on a file share. The script needs to be told the server\instance name.

I can run the Powershell in a CMDExec jobstep or an SSIS jobstep (any other possibilities?)

On the MSX server I have a table containing the destination folder names. The Powershell script reads the folder name from this table using the server\instance as the key.

How do I find the server\instance of the SQL Agent which launched the job and how do I pass it to the Powershell script?

In a Powershell jobstep I could use $thisInstance = $(ESCAPE_SQUOTE(SRVR)) but I have no idea what these words are or what language they are in.

Thanks in advance for any help

more ▼

asked May 03, 2012 at 11:10 AM in Default

avatar image

0 1 1 1

@Wilfred - I liked your first draft answer where you suggested Policy Based Management. Perhaps with that I wouldn't need to run the BPA for the auditor; I'll look into that but I also want to run Multi Server Administration jobs which can find out their instance (apart from the BPA I want to archive the error logs to a shared folder - name depends on the instance name). Thanks

@Jonathan - this changes the problem :) so I now have a TSQL job step with the instance name; I need to run a Powershell script using a proxy credential from there. I don't know how to do that.

May 03, 2012 at 12:29 PM Geoffrey
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Have you tried running a script on multiple instances using "Central Management Servers"? This will automatically add a column with the instance name.

Or "Multi Server Administration" should also be an option.

more ▼

answered May 03, 2012 at 12:01 PM

avatar image

Wilfred van Dijk
3.6k 28 39 49

(comments are locked)
10|1200 characters needed characters left

can you use the output from




to pass the necessary into your script?

more ▼

answered May 03, 2012 at 12:15 PM

avatar image

Fatherjack ♦♦
43.8k 79 101 118

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: May 03, 2012 at 11:10 AM

Seen: 1940 times

Last Updated: May 03, 2012 at 12:32 PM

Copyright 2018 Redgate Software. Privacy Policy