SQL Server Reference Guide

In this tutorial we are continuing our series on scripting, with an emphasis on real-world applications using PowerShell. There's a broader overview of scripting where I describe the difference between a scripting language and a full programming exercise.

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.