A Couple PowerShell Tips

Yesterday, I created a PowerShell script to restore a development database on a nightly basis. After getting all the kinks worked out, I decided to copy the script to one of my development SQL servers and run it as a SQL Agent job on the server.

When I ran the script via the SQL Agent, it failed with the following message:

A job step received an error at line 3 in a PowerShell script. The corresponding line is ‘Add-Type -Assembly Microsoft.SqlServer.SMO’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Could not load file or assembly ‘Microsoft.SqlServer.Smo, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified. ‘. Process Exit Code -1. The step failed.

The first several Google hits indicated that I needed to load additional components on the server, or possibly, load different components to match the server version. I downloaded and installed additional and newer components several times, but I kept getting the same error.

After more digging, I found a blogger who had gotten this error when he used the Add-Type command, but was able to run the script fine with the older, deprecated, method of loading assemblies. In his case, there were multiple different versions of SQL running on his development server. In my case, there was only one version of SQL, but I had installed the Feature Pack for SQL 2012 before SQL 2012 SP 2, so there had been multiple versions of the SMO installed.

I changed

Add-Type -Assembly Microsoft.SqlServer.SMO

for all of my assemblies to

[void][reflection.assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”)

and I moved on to a new error:

A job step received an error at line 3 in a PowerShell script. The corresponding line is ‘$space.ForegroundColor = $host.ui.rawui.ForegroundColor’. Correct the script and reschedule the job. The error information returned by PowerShell is: ‘Exception setting “ForegroundColor”: “Cannot convert null to type “System.ConsoleColor” due to invalid enumeration values. Specify one of the following enumeration values and try again. The possible enumeration values are “Black, DarkBlue, DarkGreen, DarkCyan, DarkRed, DarkMagenta, DarkYellow, Gray, DarkGray, Blue, Green, Cyan, Red, Magenta, Yellow, White”.” ‘. Process Exit Code -1. The step failed.

When a PowerShell is run as a SQL Agent Job, there is no console, so a Clear-Host command in the script causes this error. I commented out the Clear-Host, and the script is working perfectly!

A Couple PowerShell Tips

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s