2017-04-16

The PowerShell Disconnect

Dave Mason - SQL Server-The PowerShell Disconnect

Not long ago, I installed the first instance of SQL Server 2016 in a production environment. I ran some scripts to create standard SQL Agent jobs, including one that resulted in this error:

Msg 14234, Level 16, State 1, Procedure sp_verify_subsystem, Line 28
The specified '@subsystem' is invalid (valid values are returned by sp_enum_sqlagent_subsystems).

Uh-oh. I had a feeling I knew what the problem was. Yep, SQL 2016 has slammed the door on VBScript. It's no longer an option for a SQL Agent job step--I'd have to find another way. No big deal. I've written some PowerShell here and there in the past. I knew I could use it to replace the old code. Still, I was a little sad to see my old friend frozen out of SQL Server. Or maybe it was nostalgia. A couple of friends and I had a little fun with the situation on Twitter.

After a few laughs and some sad violin music, I got back to work. Soon after I started converting the VBScript to PowerShell, I think I finally embraced a realization that I've been trying to hold at bay: I just don't like PowerShell. It's not that I hate it...or that it sucks. But it continues to feel clumsy, unintuitive, and awkward. I don't feel a connection to PowerShell. This will probably be impossible to quantify, but I'll give it a shot.

Visal Studio

I got started with Visual Studio and C# many years ago. Let's say 2003-ish. You'd think that experience would help greatly with PowerShell. For instance, I know many of the objects and namespaces that would typically be needed for day-to-day PowerShell scripts. But that C# experience actually hurts my perception of PowerShell. Scripting languages sometimes play fast and loose with the rules. I'd say that's a fair assessment of PowerShell. Here's an example that I find very odd:

[String]$HW = Get-HelloWorld;
Write-Host $HW.ToString() -foregroundcolor Yellow;

function Get-HelloWorld()
{
    return "Hello World!";
} 
When that code runs the first time in the Windows PowerShell ISE, this error occurs:
Get-HelloWorld : The term 'Get-HelloWorld' 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.
Clearing the console pane and running the code a second time works!
PS C:\My Scripts\PowerShell> C:\My Scripts\PowerShell\Function Test.ps1
Hello World!

PS C:\My Scripts\PowerShell> 
The developer in me thinks this is nuts. Run the same few lines of code twice, with no changes in between, and get different outputs? Madness!

Here's another example. Nothing too complex here: I connect to an instance of SQL, SELECT CURRENT_TIMESTAMP, and show the returned value in the output window. (There's a fixable issue here that I would go on to discover later. But hold that thought for now.)

function Get-SqlHostTime( )
{
    # Get data via SqlDataReader
    $conn = New-Object System.Data.SqlClient.SqlConnection;
    $conn.ConnectionString = "Server=.\DBA;Database=DbaData;Integrated Security=SSPI";
    $conn.Open();

    $cmd = New-Object System.Data.SqlClient.SqlCommand;
    $cmd.CommandType = [System.Data.CommandType]::Text;
    $cmd.CommandText = "SELECT CURRENT_TIMESTAMP AS SqlHostTime";
    $cmd.Connection = $conn;
    $dr = $cmd.ExecuteReader();

    [DateTime]$retVal;

    if ($dr.Read())
    {  
        $retVal = $dr["SqlHostTime"];
    }

    #Clean up resources.
    $dr.Dispose();
    $cmd.Dispose();
    $conn.Dispose();

    return $retVal
}


[DateTime]$SqlHostTime = Get-SqlHostTime;
Write-Host $SqlHostTime.ToString("dddd, dd MMMM yyyy hh:mm tt") -foregroundcolor Yellow; 
Here's the runtime output:
PS C:\My Scripts\Powershell> C:\My Scripts\Powershell\DateCutoff Test.ps1
Cannot convert null to type "System.DateTime".
At C:\My Scripts\Powershell\DateCutoff Test.ps1:14 char:5
+     [DateTime]$retVal;
+     ~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : nullToObjectInvalidCast
 
Friday, 14 April 2017 05:00 PM

PS C:\My Scripts\Powershell> 
Although I only used the "return" keyword once, this variable declaration gets returned back up the stack, causing an exception:
[DateTime]$retVal;
Despite the exception, script execution continues, the variable is assigned a value, the function returns it, and the value is output to the screen. Yeah...that's weird to me. Strangely enough, if I remove the $retVal variable declaration and put it within the conditional if statement, there's no exception.
function Get-SqlHostTime( )
{
    # Get data via SqlDataReader
    $conn = New-Object System.Data.SqlClient.SqlConnection;
    $conn.ConnectionString = "Server=.\DBA;Database=DbaData;Integrated Security=SSPI";
    $conn.Open();

    $cmd = New-Object System.Data.SqlClient.SqlCommand;
    $cmd.CommandType = [System.Data.CommandType]::Text;
    $cmd.CommandText = "SELECT CURRENT_TIMESTAMP AS SqlHostTime";
    $cmd.Connection = $conn;
    $dr = $cmd.ExecuteReader();

    if ($dr.Read())
    { 
        [DateTime]$retVal = $dr["SqlHostTime"];
    }

    #Clean up resources.
    $dr.Dispose();
    $cmd.Dispose();
    $conn.Dispose();

    return $retVal
}


[DateTime]$SqlHostTime = Get-SqlHostTime;
Write-Host $SqlHostTime.ToString("dddd, dd MMMM yyyy hh:mm tt") -foregroundcolor Yellow; 
Why is that strange? Because in a strict C# environment, the equivalent code won't compile. Here's what it looks like in Visual Studio:
The compiler knows there's no guarantee the code within the if statement will get executed, therefore the retVal variable can't be guaranteed to exist. (Or in other words, the retVal variable is "out of scope".) As-is, the C# compiler won't compile or run this code. Yet the equivalent PowerShell code "works". Now, before you fill the comments with "Dave, you suck as a programmer...", let's take a step back.

Yes, I know where I went wrong. That's readily apparent when I'm in the Visual Studio world. In the Windows PowerShell ISE--it's not as obvious. As a developer, I can often draw on my experience to figure out the error of my PowerShell ways. If I run up against a problem, I generally know what questions to ask to resolve my issues. I feel sympathetic to those that are new to programming, though. How long would you bang your head on the wall for something so simple?

The foundation of PowerShell is the .NET Framework--the same as C#. Yet it feels so different and unintuitive to me. Almost a year ago, I interviewed Chrissy LaMaire about SQL Server and PowerShell. I commented "...if there is a task that is a good candidate for PowerShell, I gravitate toward writing my own solution with C#. The Visual Studio IDE is comfortable and familiar..." I was hoping I'd be a bigger fan by now. Sigh.

Debbie Downer

I suspect I'll be on the sidelines a lot with regard to SQL Server & PowerShell going forward. Maybe I can at least finish this post on a positive note...I'm seeing a large contingent of other DBAs embracing PowerShell. I see some of them debating the pros and cons of a cmdlet vs .NET Framework objects. That's a pretty kick-ass conversation to see among SQL professionals. Don't let my foibles stop you. Go check out dbatools. Follow Drew Furgiuele, Chrissy LaMaire, Mike Fal, and Rob Sewell; and check out their blogs. They're doing a ton of great stuff in the PowerShell world. I'm sure there are many others.

Update:
Give a follow to Aaron Nelson too. He maintains a good list of other PowerShell experts.

By the way, this was my solution to the previous PowerShell problem.

function Get-SqlHostTime( )
{
    # Get data via SqlDataReader
    $conn = New-Object System.Data.SqlClient.SqlConnection;
    $conn.ConnectionString = "Server=.\DBA;Database=DbaData;Integrated Security=SSPI";
    $conn.Open();

    $cmd = New-Object System.Data.SqlClient.SqlCommand;
    $cmd.CommandType = [System.Data.CommandType]::Text;
    $cmd.CommandText = "SELECT CURRENT_TIMESTAMP AS SqlHostTime";
    $cmd.Connection = $conn;
    $dr = $cmd.ExecuteReader();

    [DateTime]$retVal = New-Object System.DateTime;

    if ($dr.Read())
    {  
        $retVal = $dr["SqlHostTime"];
    }

    #Clean up resources.
    $dr.Dispose();
    $cmd.Dispose();
    $conn.Dispose();

    return $retVal
}

[DateTime]$SqlHostTime = Get-SqlHostTime;
Write-Host $SqlHostTime.ToString("dddd, dd MMMM yyyy hh:mm tt") -foregroundcolor Yellow; 


SHARE