-1

I am using Poweshell to view the status of specific SQL job. I want to pass job name as input like read-host or pass through variable. The script works fine when job name is static string in qoutes(Script 1) .In case I pass it through variable, it does not work.

If I loop through each job in Jobs and compare the job name with where-object comparison to string it works fine. That's not good way as we have 100s of jobs with second script I am getting error. Error-

Cannot index into a null array. At C:\Check status test.ps1:22 char:31 + $job = $server.JobServer.Jobs[ <<<< $job_to_check]
+ CategoryInfo : InvalidOperation: (CDSMaint:String) [], RuntimeException + FullyQualifiedErrorId : NullArray

Can anyone guide how I can have $job variable containing the object for job with name $job_to_check Any hint,guidance is highly appreciated.

Script 1:

$sqlserver="server1"
#$job_to_check='Job1'
$script:username='user1'
$script:pass_word='password'    
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
$srv.ConnectionContext.LoginSecure=$false
$srv.ConnectionContext.set_login($username)
$srv.ConnectionContext.set_password($pass_word)
if ( $job_to_check -eq $null)
{
    Write-Host "Job is null"
}
$job = $server.JobServer.Jobs["Job1"]  
echo $job.Name

Script2:

$sqlserver="server1"
$job_to_check='Job1'
$script:username='user1'
$script:pass_word='password' 
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
$srv.ConnectionContext.LoginSecure=$false
$srv.ConnectionContext.set_login($username)
$srv.ConnectionContext.set_password($pass_word)
if ( $job_to_check -eq $null)
{
     Write-Host "Job is null"
}
$job = $server.JobServer.Jobs[$job_to_check]  
echo $job.Name
Aatif Akhter
  • 2,126
  • 1
  • 25
  • 46
anupinder
  • 11
  • 1
  • 1
  • 5
  • 1
    The error is telling you that $server.JobServer.Jobs is a null array, but it's impossible to tell from what you've posted why it might be null. There's no other reference to $server. – mjolinor Oct 24 '13 at 16:18

1 Answers1

-1

I'm not sure why script 1 works but what I changed in script 2 (beside server name and login info) is the next to last line. From 

$job = $server.JobServer.Jobs[$job_to_check]
To 
$job = $srv.JobServer.Jobs[$job_to_check]
Bruce
  • 1,633
  • 1
  • 11
  • 12
  • That was typo it should be – anupinder Oct 24 '13 at 16:22
  • With that 1 change your script worked for me against a SQL 2008 R2 instance. Are you sure the job in the $job_to_check variable exists on the server and spelling is exact? – Bruce Oct 24 '13 at 16:25
  • Another point to check, if I purposely misspell the server name I get the same error message you posted. I didn’t check for a problem with the login name or password but it’s also something to verify. – Bruce Oct 24 '13 at 16:48
  • Spelling is correct and I can see its value also before I pass to Jobs and error is Cannot index into a null array. – anupinder Oct 24 '13 at 16:53