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
can you use the output from
to pass the necessary into your script?
answered May 03, 2012 at 12:15 PM
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.