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
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.