• Grant Fritchey (4/1/2013)


    Glad to hear it's fixed.

    Just curious though, why not use a PowerShell type of step in SQL Agent for a PowerShell script?

    Depending on what your script does sometimes you just can't. The SQL Agent PowerShell Step Type in 2008 R2 executes the code it is handed within a closed shell named sqlps.exe (referred to as mini-shell all over the net). There are tons of limitations but the most prominent one I could think of is that the PowerShell Step Type does not implement a default output host meaning that any CmdLet that uses Write-Host, analog to T-SQL PRINT, will throw an error like this:

    Cannot invoke this function because the current host does not implement it.

    Some of the in-built CmdLets (e.g. Remove-Item iirc) assume there will be an output host and in some cases will try to output information messages so it is simply not safe to use those in a PowerShell Step Type.

    I would need to verify but in SQL 2012 I think PowerShell Steps were changed to use a full-blown PowerShell v2 shell, i.e. no more of the limitations imposed by 2008 R2's use of sqlps.exe.

    This article explains that in 2008 R2 developing sqlps.exe solved some implementation concerns for the SQL Server team, namely locking us into a closed shell would allow them to lock down user scenarios and avoid problems with third-party snap-ins. This was great for the SQL Team in terms of being able to deliver a stable product but it created many other limitations for people like myself that like to forego T-SQL and use PowerShell for tasks that need to interact with more than just a single database engine, e.g. syncing the Server Logins from a primary instance to a DR-instance, and run them from a SQL Agent Job step.

    In 2008 R2 I end up doing the same as the OP, namely executing my ps1 scripts using powershell.exe in a CmdExec step type. Once I get more into figuring out how the changes in SQL 2012 have changed the UX I may change my approach.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato