0

I have a SQL Job that (among other things) manipulates some files in folders that are named after SQL Named Instances.

Here's the relevant code within the SQL Job:

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, 
@step_name=N'Do Stuff', 
@step_id=1,

blah blah blah...

@os_run_priority=0, @subsystem=N'PowerShell', 
@command= $dir = "\\SomeNetworkLocation\" + $env:computername
#do stuff with $dir

So this part, $dir = "\\SomeNetworkLocation\" + $env:computername, grabs the computer name and makes the full path of my file location, which works great for most of my servers. The issue I'm having is some of my servers are Named Instances, so pulling the server name isn't good enough, as it only returns the first part of the Named Instance. I need it get $env:computername + '_' + $MyInstance somehow. The format of named instances on SQL are MyServer\MyInstance, if that helps...

Is there a way to pull the named of the instance this job exists on via PowerShell?

coinbird
  • 1,202
  • 4
  • 24
  • 44
  • Have you tried using Get-SqlInstance command? – alexherm Jul 17 '19 at 16:55
  • @alexherm yeah i've been playing around with that. `Get-SqlInstance -ServerInstance "MyServer"` errors with `A parameter cannot be found that matches parameter name 'ServerInstance'.` and `Get-SqlInstance -Credential -ServerInstance "TECHPCJRF03"` prompts me to login, but even with logging in fails with `Verify that the target server is accessible and that the SQL Server Cloud Adapter service is running.` It is accessible though. I'm logged in now! – coinbird Jul 17 '19 at 17:08
  • 1
    PowerShell won't know the instance name because it would be running in the Computer context (hence why `$env:computername` works), and it won't know because you could have several named instances on a particular machine. You will have to pull the named instance from the SQL script and pass it as a parameter into the PowerShell job. – HAL9256 Jul 17 '19 at 18:59
  • @HAL9256 That makes sense. Is there a way to pass in the result of `SELECT @@servername` to the PowerShell section of the job? The PowerShell is all contained within that `@command` I posted above. – coinbird Jul 17 '19 at 20:21

1 Answers1

1

If you are using SQL Jobs, you can find the server instance by using a SQL Server Agent token to get the instance name, as well as the server name.

As a starting point, I am thinking you can get away with:

@os_run_priority=0, @subsystem=N'PowerShell', 
@command= $dir = "\\SomeNetworkLocation\" + $(ESCAPE_DQUOTE(A-SRV)) + "_" + $(ESCAPE_DQUOTE(INST))
#do stuff with $dir

I would personally set up a PowerShell script that takes in the parameters -Server and ServerInstance as the default instance would always be MSSQLSERVER, and you may need to add in a line or two to handle that.

HAL9256
  • 12,384
  • 1
  • 34
  • 46