To catch up to where we are now, in the previous tutorials we've covered
all of the concepts you need to learn about PowerShell for this exercise:

Verb-noun commands

Redirection (piping one command to another)

Variables

Objects

Providers

Functions

In the last tutorial I explained how to create a framework script that had
some comments and blocks for the two main functions we'll use in this
section, one for error handling, and the other for recording our activities in
the Event Log. That last part is what we will finish, and we'll edit the
error handling to send error information to the Event Log. Eventually we'll
put in the "work" section of the script, which is where we'll get
back to some SQL Server knowledge. The point is that when we are done with the
series, you'll be able to put any SQL Server tasks within this script, and
then combine that work with other things like operating system commands or even
other database calls (like Oracle or MySQL) to make this a truly powerful
automated solution. Keep this framework "clean" and you can use it as
a template over and over.

So let's get started. You'll recall from the previous tutorial that
we "trapped" errors, and then used the error object with its
properties to tell us what happened. We just piped all that to the screen.
We'll create the logging function, and then change that display feature in
the error handling to output variables instead, which we'll send to the
logging function.

Before we do, we need to understand a little more about how that will happen.
Again, if you're already a developer or have worked with Transact-SQL
(T-SQL) a great deal then you're already familiar with passing variables
from one function to another, but if not, here's a quick introduction.

You've already used variables in our previous examples. Go ahead and
open PowerShell and set a variable to your name:

$name = "Buck"

Remember, because it's a string, you need quotes around the name.

Now, let's make a simple function. Once again, type this in
PowerShell:

unction getname { write-host $name }

Now run it:

getname

Of course, the function just returns "Buck," or whatever you
entered as the name. To make this more useful, let's change the function to
accept an input variable. All you have to do to make that happen is just add a
set of parenthesis after the function name, and add any variables you want it to
accept, like this:

function getname($newname) {write-host $newname}

Now you can "pass" the function a variable, like this:

getname "Marjorie"

And you'll get that variable back. You can use more than one variable if
you like, just separate them with commas. When you want to use them, just send
them in that order. This brings up an interesting programming decision.

We can use either of these methods for our purposes. If you're a
developer, you'll most often pass parameters to a function. But we can also
set "global" parameters, and just let the function read those. Let me
explain.

A global parameter is something you set that your entire program can see. For
instance, if you still have your PowerShell environment up from the last few
entries I had you type, just enter $name and press ENTER. The variable
is still there, and will be until you close the PowerShell environment or alter
the variable manually. It's global. In most programming exercises, you only
set global variables for things that the entire program will use, and it's
considered bad practice to do otherwise. But remember, we're
scripting, not programming. As I mentioned in earlier tutorials,
there's a fine line between the two, and PowerShell actually is a full
programming environment. But in this case we can make an exception, since the
script we'll run isn't going to exist in a larger context. It just
runs and then finishes. Of course, if you are going to put a lot of
code in a script or you are going to run more scripts after this one, you might
want to use a variable-passing technique rather than just setting a variable in
one function and using it in another.

I'll combine the two here in this script without making a judgment
either way and you can choose the method you like best. I'll set the name
of the area we're working with as a global variable, and I'll pass the
logging information in the error handling function to the logging function. That
way we'll record errors not only for the code that failed, but we'll
know generally where it failed in our script. We get information that went wrong
in the code (debugging information) and failures in the process (errors) all at
the same time.

With all of that sorted out, let's first work on the code that takes
variables for the Event Logs and then records them. I've already covered
the Event Log process in
one of the earlier PowerShell tutorials,
so in this one I'll trim out the comments I had there so that this is a
little more readable. we'll actually create the Event Log object outside of
the function, since there's no reason to keep re-creating it each time we
log something. We can make it once and just reuse it over and over, so
we'll put it right at the top.

In addition to the log object, we need five variables:

Log Type: This is where we want to send the information. We can use
the Security Log, the Application Log, the System Log and so on. We'll call
this one $logType.

Program Source: This is the name of the program that we want
recorded in the log. This is the global variable, and we'll call this one
$programSource.

Event ID: This is a number that we set so that we can search for the
errors or entries specifically. The best thing to do is to record a specific
number for each type of event we can think of, such as "Process
starting," or "File not Found." For now we'll just set this
to a single number that means "My backup program." We'll call
this one $logEventID.

Type: This could be a warning or an informational type message.
We'll call this one $logEntryType.

Message: This is the text of what we want in the log entry.
We'll build this string out of the error message information, and
we'll call this one $logMessage.

We're ready to code. Let's bring in the script as it stands so far
and enter the information in the logging function:

# TransferDatabase.PS1
# Backup, Compresses, Copy and Restores a Database from one Server to Another.
# Buck Woody
# Last changed: 07/13/2007
# Requires an XML file called "TransferDatabases.XML"
#in the current directory
# Get the Event Log Object, since we’ll need it throughout the script:
$log = New-Object System.Diagnostics.EventLog
# Logging Process Function
function LogThis($logType, $logEventID, $logEntryType, $logMessage)
{
# Set the type and source
$log.set_log($logType)
$log.set_source($programSource)
# Write the information to the approriate log
$log.WriteEntry($logMessage, $logEntryType, $logEventID)
}
# Error Handing Function
# In this case we're just writing the error info out
# to the screen. Later we'll log it so we can get the
# error information even though no one is at the console.
function ErrorHandler
{
write-host "Error Category:" $error[0].CategoryInfo.Category
write-host "Object being worked on:" $error[0].TargetObject
write-host "Error Message:" $error[0].Exception.Message
write-host "Error Message:" $error[0].FullyQualifiedErrorId
}
# Do some work
Trap {
# Go to the error handler
ErrorHandler
# Stop the program.
break;
}
# We need to cause an error. Let’s make up a fake command:
# RockMeAmadeus!

I've commented out the error until we've edited the error handler
to actually send a message. If you save and run this script you shouldn't
receive any errors or any output, since right now it doesn't do anything.
Let's move on to editing the error handler to send output to the logging
function rather than the screen.

This is a fairly straightforward process. Read the comments in this script,
and then save and run the program. Check your Application Log and you should see
the error information displayed:

# TransferDatabase.PS1
# Backup, Compresses, Copy and Restores a Database from one Server to Another.
# Buck Woody
# Last changed: 07/13/2007
# Requires an XML file called "TransferDatabases.XML"
#in the current directory
# Get the Event Log Object, since we’ll need it throughout the script:
$log = New-Object System.Diagnostics.EventLog
# Logging Process Function
function LogThis($logType, $logEventID, $logEntryType, $logMessage)
{
# Set the type and source
$log.set_log($logType)
$log.set_source($programSource)
# Write the information to the approriate log
$log.WriteEntry($logMessage, $logEntryType, $logEventID)
}
# Error Handing Function
# In this case we're just writing the error info out
# to the screen. Later we'll log it so we can get the
# error information even though no one is at the console.
function ErrorHandler
{
# We’ll use Application for everything here, but you can use others if you like
$logType = "Application"
# We’ll use a single number here, but you can use anything you like
$logEventID = 7777
# For errors we’ll use Error
$logEntryType = "Error"
# And we’ll build the message string with a carriage return at the end of each line
$logMessage = "Error Category:" + $error[0].CategoryInfo.Category
$logMessage = $logMessage + "´rObject being worked on:" + $error[0].TargetObject
$logMessage = $logMessage + "´rError Message:" + $error[0].Exception.Message
$logMessage = $logMessage + "´rError Message:" + $error[0].FullyQualifiedErrorId
# Send the information to the Logging function
LogThis $logType $logEventID $logEntryType $logMessage
}
# Do some work
# Set the global variable of where we are in the code
$programSource = "Main Block"
Trap {
# Go to the error handler
ErrorHandler
# Stop the program.
break;
}
# We need to cause an error. Let’s make up a fake command:
RockMeAmadeus!

The reverse apostrophe and r (´r) gives you a line break in front of the
command. We're going to use logging for more things than just error
messages, but adding these two sections to your SQL Server PowerShell scripts
will at least give you some tracking options. In the next tutorial we'll
add some SQL Server calls and begin to put our script to work.

InformIT Articles and Sample Chapters

PowerShell is built on top of the .NET infrastructure, so you can use any of
those constructs here. There's a great reference for Event logging in .NET
here.

Online Resources

If you haven't run across this reference yet, it's a great resource
to have. It's a
quick start to PowerShell,
and contains almost everything you need to get started.