Using PowerShell in the SQL Agent
I needed to schedule a PowerShell script so decided to use a SQL Agent job. Doing so showed up some unexpected results. SQL Server isn’t using the latest and greatest PowerShell version when you call it from job using the agent.
SQL Server 2008 Agent users PowerShell 1.0
SQL Server 2008 r2 Agent uses PowerShell 1.0
SQL Server 2012 Agent users PowerShell 2.0
A quick way to check is below:
Create a new Agent Job in a one of the above versions of SQL Server
In the Job Step Use PowerShell and script
Get-Host (This returns the version of PowerShell in use)
Under Advanced add an Output file (make sure the Output file is in a location that the Agent can write to.)
Click OK and then run the Job.
Once its completed open up the Output file (I ran it on a SQL Server 2012 box and got the below)