Powershell debugging

Published 18 January 11 04:15 PM | MartinBell

I have to admit that I am still quite stick in my Powershell 1.0 ways when it comes to writing scripts. That is I write the script in textpad and paste it into the command prompt window. Many times I have pasted a script and wondered why I still have a continuation prompt then realise I have missed out a closing quote.

When it comes to debugging this makes using Powershell a bit of a pain! For instance with the following script I have missed out a “-“ in write-host:

# load assemblies
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SqlEnum")
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")
 
function fn_getdbs ( [string] $server, [string] $userName, [string] $password ) {
 
    try {
        $conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection ;
        $conn.LoginSecure = $false ;
        $conn.Login = $userName ;
        $conn.Password = $password ;
        $conn.ServerInstance = $server ;
        $conn.NonPooledConnection = $true ;
        $srv = New-Object Microsoft.SqlServer.Management.Smo.Server ($conn) ;
        foreach($j in $srv.Databases) {
            writehost $j.name ;
        } ;
    }
    catch {
        $err = $Error[0].Exception ;
        write-host "Error caught: " $err.Message ;
        continue ;
    } ;
}

Because Powershell is interpreted I will not get an error if there are no databases on the instance or if I don’t type in the credentials correctly. The error message I do get is:

Error caught:  The term 'writehost' 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.

If the code was longer or more complex finding just where the error is could be difficult, but as I have written it in textpad I can search for “writehost”.

To step through the script you can use the cmdlet set-PSdebug. There are two methods of calling this cmdlet, one to turn debugging on:

Set-PSDebug [-Step] [-Strict] [-Trace <int>] [<CommonParameters>]

and one to turn it off:

Set-PSDebug [-Off] [<CommonParameters>]

Using the –step option will allow you to step through a script and you can see the how the script is executed. In the above example this would show where the exception that caused the catch block to be invoked originated and you would then be able to find the line and change the script. I used this in conjunction with SQL Profiler to monitor the login attempts when investigating my previous blog post. Even the initial call to set-PSdebug will be traced!! After each step you will be prompted for a response, this can get a bit tedious:

PS C:\Users\Martin> set-psdebug -step

Continue with this operation?
   2+         $foundSuggestion = <<<<  $false
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):?
Y - Continue.
A - Continue and do not inquire to continue again for this session.
N - End the operation with an error.
L - End the operation with an error and do not inquire to continue again for
this session.
S - Pause the current operation and enter a command prompt. Type "exit" to
resume the paused operation.
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):

You may be tempted to use the Yes to All option, but remember this is scoped at the current debug session and subsequent commands will not be stepped through unless you set-PSdebug –step again.

Using the –S option allows you to enter another shell and examine and (sometimes) set variables e.g. With the script:

function fn_setgetvar ([string] $value)  {
    $var = $value
    write-host $var
}

I can do the following:

PS C:\Users\Martin> fn_setgetvar "Hello World"

Continue with this operation?
   1+  <<<< fn_setgetvar "Hello World"
[Y ]  Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    1+  <<<< fn_setgetvar "Hello World"

Continue with this operation?
   2+     $var = <<<<  $value
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    2+     $var = <<<<  $value

Continue with this operation?
   3+      <<<< write-host $var
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):S
PS C:\Users\Martin>>> $var
Hello World
PS C:\Users\Martin>>> $var = "What is this?"
PS C:\Users\Martin>>> $var
What is this?
PS C:\Users\Martin>>> exit

Continue with this operation?
   3+      <<<< write-host $var
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    3+      <<<< write-host $var
What is this?

Continue with this operation?
   2+         $foundSuggestion = <<<<  $false
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    2+         $foundSuggestion = <<<<  $false

Continue with this operation?
   4+         if <<<< ($lastError -and
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:    4+         if <<<< ($lastError -and

Continue with this operation?
  15+         $foundSuggestion <<<<
[Y ] Yes  [A ] Yes to All  [N ] No  [L ] No to All  [S ] Suspend  [? ] Help
(default is "Y"):
DEBUG:   15+         $foundSuggestion <<<<

The highlighted line shows the change value of the variable $var. This is the output from looking at the $conn before creating the server object in the fn_getdbs function. Unfortunately for a connection object you can’t change the values after the connection has been made so if I tried to change the ServerInstance you get an error:

PS C:\Users\Martin>>> $conn.ServerInstance = "(local)"
Exception setting "ServerInstance": "Connection properties cannot be changed af
ter a connection has been established."
At line:1 char:7
+ $conn. <<<< ServerInstance = "(local)"
    + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
    + FullyQualifiedErrorId : PropertyAssignmentException

The –trace parameter can take one of three values:

-Trace <int>
    Specifies the trace level:
     0 - Turn script tracing off
     1 - Trace script lines as they are executed
     2 - Trace script lines, variable assignments, function calls, and scripts.

To avoid having to step through all the lines in the fn_getdbs function I can use the cmdlet set-PSbreakpoint to stop the script at a given point. An example to break on the assignment to $srv variable can be found here. Once you hit the breakpoint you can examine/set the values of variables. There are companion commands get-PSbreakpoint, enable-PSbreakpoint, disable-PSbreakpoint and remove-PSbreakpoint. These were introduced in Powershell 2.0 which also has the GUI Powershell ISE interface that allows you to set/clear breakpoints using F9 in the script pane






If I run the script (F5) and follow the previous example of examining and changing $var to “What is this?” you get the following in the Output window

In Powershell ISE when you reach the breakpoint, you can view the value of a variable by placing your cursor over the variable in the script window and it will be displayed as a tooltip.

Hopefully this post will be useful when you want to fathom out what your Powershell scripts are doing! In my next Powershell post I will look at how to log debug information.

Filed under:

Comments

# Martin Bell (MartinBell) posts Powershell debugging | sqlmashup said on January 18, 2011 08:53 PM:

Pingback from  Martin Bell (MartinBell) posts Powershell debugging | sqlmashup

# Dew Drop – January 19, 2011 | Alvin Ashcraft's Morning Dew said on January 19, 2011 02:00 PM:

Pingback from  Dew Drop &ndash; January 19, 2011 | Alvin Ashcraft&#039;s Morning Dew

# Martin Bell UK SQL Server MVP said on January 22, 2011 10:44 PM:

In my previous Powershell blog post I talked about debugging Powershell scripts using Set-PSDebug and Set-PSBreakpoint, but there are certain circumstances where you may not be able to do that. This is where writing to a log file may be useful.

# roof anchors said on October 25, 2014 12:23 PM:

Powershell debugging - Martin Bell UK SQL Server MVP

This Blog

SQL Blogs

Syndication