PowerShell SQL Server Agent Uses Version 2

Just as an FYI so there is no confusion: If you intend to use the PowerShell module packaged with SQL Server Agent 8, 12, 14, or 16, you must be aware it uses its own version of PowerShell regardless of the version you have installed! This can be very confusing if you use Cmdlets which require PS Version 3 and above.

Take a look at the two images below. The first sets up a PowerShell “type” job which only includes the command to display the current PowerShell version (i.e. $PsVersionTable.PsVersion). The second shows the job result form Job History. You can clearly see the PowerShell version used I version 2. Yet, I do have PowerShell version 5 installed on my PC . The version of SSMS I am using is 2016. The SQL Server I am referencing is 2008r2. However, you get the same result with SQL Server 2008 through 2016.


SQL Agent POSH V 2

Setting Up Your Old Powershell on a New Windows Install

Let me start off by saying “when setting up your PowerShell in a new OS environment, be sure to load your assemblies first otherwise you may not see ANY commands in the command viewer”.

This is just an FYI for anyone who does a “clean” install of Windows 10 and then wants to set up their PowerShell similar to what they had.

I use PowerShell with SQL Server almost exclusively. My environment had a profile, a module of frequently used functions for SQL Server, and a folder with numerous POSH scripts which was included in my path.

After installing Windows 10, I fired up PowerShell ISE and found it to be working perfectly. After adding my module folder to the PsModulePath and adding my scripts folder to the Path, I found I could no longer view ANY commands in the ISE command viewer! Working backwards, I took my scripts library out of the Path and restarted the ISE. Lo and behold, the command viewer now showed the commands again.  After adding my scripts folder back to the path, command viewer would not display any commands.

I decided to load my SQL Server assemblies, performed a refresh on command viewer and made some progress. Now an error message popped up regarding an error in one of my scripts (in the path). After correcting several script errors, all available commands now showed up in command viewer.

I restarted PowerShell and the commands were no longer viewable again. I then added the loading of my SQL Assemblies into a profile and after restarting, all commands were once again visible in command viewer.

It would appear that the new PowerShell does some type of error checking for scripts in your path and if the scripts reference assemblies not loaded, it breaks down with regard to Command Viewer.

Canon PIXMA MX870 Print Drivers Not Installing on Windows 10

This is for anyone having a problem installing the print drivers for a Canon Pixma MX870 printer. I have two machines, one is new and came with Windows 10 installed and the other had Windows 10 installed as an upgrade after restoring it to the OEM Windows 7 Home.

The newer downloaded drivers from Canon installed with no problem on the new machine but aborted with an error on the upgraded machine. The error reported was “Canon IJ Driver Installer Has Stopped Working”. After re-downloading, uninstalling, re-installing, restarting, googling, etc. I contacted Canon support. They were very responsive and after several attempts, we finally got the install to complete without error and the printer and software are working great.

The final resolution is listed in this excerpt from a Canon Support email:

  1. From the Start menu, click Settings, Devices, Printers & Scanners.
  2. Towards the bottom of the Printers & Scanners area, turn off Let Windows manage my default printer.
  3. Once this has been done, re-install the drivers again.

You may also have to delete any extra icons of the printer in the Printers & Scanners menu.

PowerShell–Verify AD Principal

I recently needed to add a function which would verify if an Active Directory principal exists. I also did not know if the principal is a Group or a User.

My first attempt was to use the straight forward cmdlets Get-AdGroup and Get-AdUser in an “If” statement block (i. e. If Group is not found then try User).  My script looked like this:

If(!(Get-ADGroup -Identity $Principal)) {
    If(!(Get-ADUser -Identity $Principal)) {
        Throw "AD principal $principal was not found"
Write-Host "AD principal $principal found."

I found out immediately that when Get-AdGroup gets an error it writes an error message and the entire script block (everything under the first IF and including the second IF) gets ignored and the following statement “ Write-Host” gets executed. Because a not found condition is not a terminating error, adding –ErrorAction SilentlyContinue has no effect.

What I ended up doing is adding an -ErrorAction Stop to each cmdlet. That way I could use Try{} Catch{} to trap it at Get-AdGroup and use the trap to then check if the principal is a user using Get-AdUser. The final code looks like this:

Try {Get-ADGroup -Identity $Principal -ErrorAction Stop} 
    Catch { Try {Get-ADUser -Identity $Principal -ErrorAction Stop} Catch { Throw $_ }}

Write-Host "AD principal $principal found."

Know When SQL Server Availability Group Fails Over by Using Powershell

We recently started using HAG (High Availability Groups) in SQL Server and had several unplanned failovers. The failovers occurred for various reason which this article does not present. What was relevant to us was the failovers occurred without our knowledge and in one case, several days passed before we were aware a group had failed over to a secondary instance.

The purpose of this article is to show what was done to make us immediately aware of a failover as it occurs. This is accomplished by identifying an event occurrence associated with a failover and associating with a Windows Event Task. The event task would  then send an email containing the event message to a responsible group or person.

The event which I identified as being most relevant is Event 19406 from the SQL Instance (in this case the default instance of ‘MSSQLSERVER’) as presented here

I already knew how to attach a Windows Task to an event but what I did not know was how the data from the event is passed to the task. I wanted the task to be able to provide the event data in an email. In researching this I came across a great article on this very subject. The article presented all the information I needed including Powershell source code. I had to tweak the source code ever so slightly for my own use. The full article can be found at this link http://blogs.technet.com/b/wincat/archive/2011/08/25/trigger-a-powershell-script-from-a-windows-event.aspx and you should follow it. There is essential information you will need to know to edit and add to the Windows Task. Kudos to OttoHelweg2 for this fine article.

And, here is the source code I used based on the above mentioned article


$xpath = "<QueryList><Query Id='0' Path='$eventChannel'><Select Path='$eventChannel'>*[System[(EventRecordID=$eventRecordID)]]</Select></Query></QueryList>"
$event = Get-WinEvent -LogName $eventChannel -FilterXPath $xpath
$message = $event.Message

Send-MailMessage -From AvailabilityMonitor@myorganization.com -Subject "An SQL Failover is in Progress" `
    -To SQLServerGroup@myorganization.com -Body $message -SmtpServer mailserver.myorganization.com

The $eventRecordId and $eventChannel are passed to the Windows task from the triggering event and are used by Powershell to obtain the actual event record. The “message” data is then used as the Body of a Send-MailMessage cmdlet.

As mentioned, we are trapping the 19406 event. When reviewing the Event list, you will notice the event will fire several times in response to a failover. However, because the event is generated by the same task, the task associated with the event only fires once.