PowerShell scripts works in ISE but fails as SQL agent job.

Hi everyone.

Could anyone offer my any advice on why this works when run in ISE but fails in SQL Agent job.

Script is :

$collection = $()
foreach ($server in get-content "C:\SQLSkills\PowerShell\All_SQL_Servers_Ping_names.txt")
{   
$status = @{ "ServerName" = $server; "TimeStamp" = (Get-Date -f s) }
if (Test-Connection $server -Count 1 -ea 0 -Quiet)
{        
$status["Results"] = "Up"
}    
else    
{        
$status["Results"] = "Down" 
}   
New-Object -TypeName PSObject -Property $status -OutVariable serverStatus
$collection += $serverStatus}$collection | Out-File C:\SQLSkills\PowerShell\pingresults.csv

Works in ISE as I say but fails when run in agent job. Agent account has privs to do task. Error message I get is :

Message
Unable to start execution of step 1 (reason: line(1): Syntax error).  The step failed.

 

Any help much appreciated.

 

Thanks

 

 

  • SQL Agent Jobs use T-SQL syntax I believe. But certainly not PowerShell.

    You'll need to re-write it in the correct language or run the PowerShell job from Task Scheduler or something like that.

  • Hey Mickey,

    SQL Server launches PowerShell jobs in Version 2.0 context, including SQL 2014

    It's failing on the first line.  Change it to :

    $collection = @()

    And you should be fine.

    cheers,

     

    Tim

  • Hi Tim thanks for quick reply.

    Changed as discussed and it runs for longer (26 seconds compared to fail straight away) but now fails with :

    A job step received an error at line 6 in a PowerShell script. The corresponding line is 'if (Test-Connection $server -Count 1 -ea 0 -Quiet)'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'Test-Connection' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again.  '.  Process Exit Code -1.  The step failed.

    ?

    thanks for you help.

     

  • Hmmm strange, it works okay on my system.  The only other things I've done are to setup a credential object and proxy, but it also runs using the default agent service.

    I'm running SQL 2012 CU8 on Win2KR12. It should still run in PS 2.0 context, so those really shouldn't matter.

    Just as a matter of checking, can you copy and paste the exact code below?

    NB. The $servers change was just for my own use, so it shouldn't affect the code anyway.

     

    $collection = @()
    $servers = @("$env:computername","$env:computername","$env:computername")
    foreach ($server in $servers)
    {  
        $status = @{
            'ServerName' = $server
            'TimeStamp' = (Get-Date -Format s)
        }
        if (Test-Connection $server -Count 1 -ea 0 -Quiet)
        {
            $status['Results'] = 'Up'
        }   
        else   
        {
            $status['Results'] = 'Down'
        }  
        New-Object -TypeName PSObject -Property $status -OutVariable serverStatus
        $collection += $serverStatus
    }$collection | Out-File -FilePath C:\SQLSkills\PowerShell\pingresults.csv