x

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

Geoffrey gravatar image

Geoffrey
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 newest

can you use the output from

SELECT @@SERVERNAME

or

SELECT @@SERVICENAME
to pass the necessary into your script?
more ▼

answered May 03, 2012 at 12:15 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.7k 75 79 108

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

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

Wilfred van Dijk gravatar image

Wilfred van Dijk
1.3k 19 23 30

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

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x588
x28
x9
x6

asked: May 03, 2012 at 11:10 AM

Seen: 1419 times

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