SQL Server Reference Guide

In a previous tutorial I explained how you can use the Web Assistant Wizard in SQL Server version 2000 to create web pages that show the status of your maintenance plans. It's a great tool, backed by several stored procedures that actually do the work.

In SQL Server 2005, Microsoft removed this Wizard. They did so not because people didn't use the feature, but because they provided something else. The reason they did that the Web Assistant Wizard only creates one kind of report output — HTML — and it had very limited formatting at that. You didn't have sub-reports, collapsible bands and so on. Granted, HTML has that, but the Wizard would have gotten too complex to create fancy web pages.

In addition, what is needed is a broader solution, not just a single fix. What we really want is a way to report things out of the system. So we received this feature in the form of Reporting Services, which I've described in another set of tutorials and overviews.

However, even this solution has its drawbacks. You now have another tool to learn, and you need to learn to create reports, not just select output. It seems that in providing a more advanced solution, we've lost the simple one.

But there's hope. One great thing about Microsoft products is that there is always another way to do something. In fact, there are usually several ways to do something. In this case, we don't have to rely on the Web Assistant Wizard to create web pages from SQL Server — we can do it with many other technologies. Some of those technologies include other graphical tools and programming constructs. They also include scripting.

I've covered various kinds of scripting technologies in this guide. One of the latest releases from Microsoft is PowerShell, which has a lot of advantages in scripting languages. I've covered the basics of PowerShell in another series of tutorials, which I highly recommend that you read before you continue on here. I won't be covering the same information in this tutorial, and I'll assume some knowledge such as how to get and install PowerShell, the basic structure of the verb-noun "command-lets" and so on. We'll be using those constructs along the way in this article.

Another advantage in using a scripting language (and in particular, PowerShell) is that scripting is well-suited for multiple-server management. Years ago I used the Perl scripting language to manage all my systems. That's because I had Windows servers, UNIX systems, Oracle and SQL Server databases and more. Perl answered that call with lots of system support, and it ran on multiple operating sytems.

I use PowerShell now because I'm mostly on Windows systems and it provides a lot of support for all of the Windows systems I work with, most notably Exchange. Exchange has switched their entire management substructure on PowerShell, using hundreds of command-lets to do everything from creating a mailbox to assigning a user.

So one way you can check your maintenance plans in SQL Server is to use PowerShell to create web pages showing the output of a query, and in this tutorial I'll show you how.

Finding the Information Manually

We have our goal, and now we need to come up with the process to get there. Before I automate anything, I make sure I can do it manually. In the tutorial where I explained the Web Assistant Wizard, I gave you a query for the table that stores the history information for the maintenance plans:

/* Read the Maintenance Plan history for SQL Server 2000 */
SELECT ’Succeeded: ’ + CAST(succeeded AS VARCHAR(1))
,’Completed on: ’ + CAST(end_time AS VARCHAR(11)) 
,’Database Name: ’ + database_name 
,’Activity: ’ + activity 
,’Duration: ’ + CAST(duration as varchar(1)) 
,message 
FROM sysdbmaintplan_history 
WHERE DATEDIFF(day, end_time, getdate()) < 1 
ORDER BY succeeded, database_name

This script, however, only reads the history for plans created by SQL Server 2000. In fact, there were several tables that held historical information in SQL Server 2000:

• sysdbmaintplans
• sysdbmaintplan_jobs
• sysdbmaintplan_history
• sysdbmaintplan_databases

If you're using SQL Server 2005, this table won't have anything in it — unless, of course, you upgraded from 2000, in which case that plan history will still be there. In SQL Server 2005, things got a bit more complicated. Microsoft has split out the history into multiple tables, and one view:

• sysmaintplan_subplans
• sysmaintplan_log
• sysmaintplan_logdetails
• sysmaintplan_plans (really a view over table msdb.dbo.sysdtspackages90)

So to get the same information we got out of one table before we have to make a few joins:

/* Read the Maintenance Plan history for SQL Server 2005 */
SELECT 
mpd.server_name AS [ServerName], 
mpd.line1 AS [Name], 
mpd.line2 + ’, ’ + mpd.line3 + ’, ’ + mpd.line4 AS [Description], 
mpd.start_time AS [Start Time], 
mpd.end_time AS [End Time], 
mpd.error_number AS [Error Number], 
mpd.error_message AS [Error Message], 
mpd.command AS [Command], 
mpd.succeeded AS [Succeeded] 
FROM msdb.dbo.sysmaintplan_plans AS s 
INNER JOIN msdb.dbo.sysmaintplan_subplans AS sp 
  ON sp.plan_id=s.id 
INNER JOIN msdb.dbo.sysmaintplan_log AS spl 
  ON spl.subplan_id=sp.subplan_id 
INNER JOIN msdb.dbo.sysmaintplan_logdetail AS mpd 
  ON mpd.task_detail_id=spl.task_detail_id
ORDER BY [Start Time] DESC

If you have maintenance plans set up and they have run more than once, you can run these scripts to produce any output. It's important to take this step first, so that you are sure the script we create later is working, and to test the SQL script itself.

When you have some output that you're satisfied with, , create a text file with one of these scripts in it on your system called SQLText.TXT in your C:\TEMP\ directory. If you want to use a different directory or filename, just remember what I'm calling them here.

Creating a text file is useful because once we're done, you can put any query in here that you want — as long as it produces some kind of result, it can be used to create a web page.

Automating with PowerShell

So let's review — we have a goal in mind (a web page showing the maintenance history), we have a manual process that works (the Transact-SQL statements from above) and we're ready to automate the process.

Now all we have to do is ensure that PowerShell is installed and configured to run a script. Remember, you have to set the execution policy properly to be able to run a script within Powershell, since by default it won't run them. Remember that we have that C::\TEMP\SQLText.TXT file on the drive with the right statements in it for the version of SQL Server you care about.

I'll show you the completed script in a moment, but let's take this step-by step. You can actually copy these lines one by one, and make the statements work as a set of statements by pressing ENTER after each one. In the final step I'll show you how to make it a full, reusable script.

Making the connection

First, we need to make a connection to the server. To set this up for a repeatable, extendable script, I'm going to assign quite a few variables. Using variables also simplifies the typing — and I'm all about simplifying typing.

We'll assign a variable first to the connection, using the built-in database library in PowerShell:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=(local);database=msdb;Integrated Security=sspi"
$sqlConnection.Open()

There are a couple of things to notice here. First, we used the System.Data.SqlClient method to connect as a new system-object. There are other libraries you can use, but in general I find this latest library to be the best for fast results.

Second, I've got the server hard-coded to (local). Hard-coding anything is almost always a bad idea, but to keep this example simple I'll just run this locally on my test system. To make the script truly extensible, you should follow the steps in my other tutorial on PowerShell and feed the server name to the script either from the command-line as a variable or even better as a series of servers from a text file. For this script, I leave the database hard-coded as well since the maintenance plan history is in the msdb database. Of course, you can also parse through a text file to set through the database, but we'll save that for another tutorial.

I'm also using Integrated Security, so I'm going in with the credentials of the person running the script. I think this is the simplest and safest way to operate, but you can see my other tutorials if you want to use SQL Authentication.

In the second line we simply open the connection we just created.

Creating a Command

So we've made a connection, and it is sitting there open and waiting for us to do something. There are actually several things we could do at this point, from scripting out tables to starting a backup. Instead, we're going to create a new object called a SQL Command. This object is ready to take commands and send them on to the server, and potentially even store what is returned, whether that is the message (this many records, errors, and so on) or the records from a query. Here is the command that create the new object:

$sqlCommand = New-object system.data.sqlclient.SqlCommand   

Now we need to set a few parameters. My test system is pretty slow, so I'll set a 30 second timeout:

$sqlCommand.CommandTimeout = 30 

You might want to leave that even for a fast server, in case you're working across a network.

Now let's set the command to use that connection we made a moment ago:

$sqlCommand.Connection = $sqlConnection

The final option to set is the CommandText — the Transact-SQL code we want to run. Here's where that SQL Script we made earlier comes in handy. I'm going to set the CommandText parameter to a PowerShell function that reads a text file, called get-content. I'll tell that command to read the SQLText.TXT file we made:

$sqlCommand.CommandText = get-content c:\temp\SQLText.txt 

Creating the Result Container

So we've set up a connection and sent a set of commands from a text file to a command object. So far, so good. Once again, if you're trying this out line by line you'll be able to tell if you made any mistakes and where.

Now let's create one more object: a "data adaptor" that holds the results of the command object:

$sqlDataAdapter = new-object System.Data.SqlClient.SQLDataAdapter($sqlCommand) 

And now we create a "bucket" to hold the resulting data set that was filled by the adaptor:

$sqlDataSet = new-object System.Data.dataset 

And then fill it:

$sqlDataAdapter.fill($sqlDataSet)

And then get the tables out of what we filled:

$sqlDataSet.tables[0].select()

Creating the Web Page

We're almost done — we have all the data, ready to put somewhere. We can use another PowerShell command called ConvertTo-HTML that takes an input and creates an output, with HTML options.

Don't run this command, however. We need to have a mechanism to feed our SQL info into this command, and we can't do that until I show you how to put this all together. we'll get to that in a minute. The command, however, looks like this:

ConvertTo-HTML -title "Maintenance Plan" -head "<head><style type=’text/css’> body {font-family: sans-serif; font-size: 9pt;} td {vertical-align: top; color: blue;} </style> </head>" | Out-File C:\temp\Maintreport.html

First, I've set a title as you can see. Second, using the –head option, I've sent what would normally be embedded in the <HEAD> element of HTML. In this case, I've used some Cascading Style Sheet (CSS) options to make the table elements more readable.

Finally, I've used the "pipe" symbol ( | ) to send the whole thing to an HTML file. I'm putting that in the TEMP directory as well, but you can put it anywhere you want.

Putting it all together

So now we need a way to take everything from the SQL Statement side and put it into the ConvertTo-HTML command. We need two things to do this: a way to bundle the SQL tables and a way to send them to the PowerShell command. The first part can be taken care of by making those SQL commands a function. I explained how to make a function in my other tutorial, but it is quite simple. At the top of a file we'll make called "c:\temp\Checkmaint.ps1" we start off with a function name:

function CheckMaintenance { }

In between the braces we place all the commands needed for the SQL tables to be filled:

function CheckMaintenance 
{
  $sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=(local);database=msdb;Integrated Security=sspi"
  $sqlConnection.Open()
  $sqlCommand = New-object system.data.sqlclient.SqlCommand   
  $sqlCommand.CommandTimeout = 30 
  $sqlCommand.Connection = $sqlConnection
  $sqlCommand.CommandText = get-content c:\temp\SQLText.txt 
  $sqlDataAdapter = new-object System.Data.SqlClient.SQLDataAdapter($sqlCommand) 
  $sqlDataSet = new-object System.Data.dataset 
  $sqlDataAdapter.fill($sqlDataSet)
  $sqlDataSet.tables[0].select()
}

And then we can call the function, and once again using the pipe symbol, we send the results to the HTML command, which sends the results on to the text file:

CheckMaintenance | ConvertTo-HTML -title "Maintenance Plan" -head "<head><style type=’text/css’> body {font-family: sans-serif; font-size: 9pt;} td {vertical-align: top; color: blue;} </style> </head>" | Out-File C:\temp\Maintreport.html

That's it. Here's the whole thing put together:

function CheckMaintenance 
{
  $sqlConnection = new-object System.Data.SqlClient.SqlConnection "server=(local);database=msdb;Integrated Security=sspi"
  $sqlConnection.Open()
  $sqlCommand = New-object system.data.sqlclient.SqlCommand   
  $sqlCommand.CommandTimeout = 30 
  $sqlCommand.Connection = $sqlConnection
  $sqlCommand.CommandText = get-content c:\temp\SQLText.txt 
  $sqlDataAdapter = new-object System.Data.SqlClient.SQLDataAdapter($sqlCommand) 
  $sqlDataSet = new-object System.Data.dataset 
  $sqlDataAdapter.fill($sqlDataSet)
  $sqlDataSet.tables[0].select()
}

CheckMaintenance | ConvertTo-HTML -title "Maintenance Plan" -head "<head><style type=’text/css’> body {font-family: sans-serif; font-size: 9pt;} td {vertical-align: top; color: blue;} </style> </head>" | Out-File C:\temp\Maintreport.html

Save that file, and then from PowerShell run this command:

./Checkmaint.ps1

As long as you have the T-SQL statements set up properly in the right location, you'll produce a web page showing your maintenance.

You can adapt this script to read multiple server outputs in several ways. I do it by using the connection over and over and concatenating the results to a variable before I send that to the HTML command.

InformIT Articles and Sample Chapters

I've got an entire series on PowerShell referenced in the article, but there's a great reference book here.

Online Resources

There's a free PowerShell book from Microsoft here.