SQL Server Reference Guide

There are a lot of ways to get data out of SQL Server, including the BCP program, the Import and Export Wizards, Data Transformation Services (DTS) in SQL Server 2000 and SQL Server Integration Services in SQL Server 2005 and 2008. But many times you don’t want the data out of the server, you just want to read it in another format. And for portability, ease of access and distribution, almost nothing beats a web page.

SQL Server provides public data access in many ways, and if you’re looking to create a web page, the standard method is to use Reporting Services. I’ve explained how to use Reporting Services elsewhere, so I won’t cover that ground here. This tutorial covers a method that creates web pages with a little less setup — and no requirement for a Reporting Services instance to be set up.

Why do that, when all you need to do is install Reporting Services and create a few reports? And why is this tutorial right in the middle of the Administration section?

The same reason holds for both. First, you don’t always have the option of installing and setting up a Reporting Services instance. And second, you might just want that data in HTML for a quick report on the system status or maintenance. For that, it seems a little extreme to set up a Reporting Services instance. But as the “data steward,” you want to track things on your server like service pack levels, user accounts, data structure changes and so on. You probably know how to do this using Transact-SQL statements by polling various system stored procedures and views.

In this tutorial I’ll show you how you can check something that you might have in place on your own systems: your database maintenance plans histories. Although I’ll use the tables that show that information, you can create web pages that show the results using any Transact-SQL result that you want.

In an earlier tutorial I explained a bit about database maintenance, and in another tutorial I showed you how to use the SQL Server Maintenance Plan Wizard to automate maintenance steps into a full maintenance plan, using the graphical tools in SQL Server.

A maintenance plan, as I explained, is really just a set of SQL Server Agent Jobs in SQL Server 2000, and in 2005 and higher you can use SQL Server Integration Services (SSIS) packages with specific operations and flow to create maintenance plans.

The Maintenance Wizard has three ways to notify you that the jobs have completed and whether they were successful. Of course, with either version you can edit the jobs or steps that you create with the wizards.

The first method is the e-mail notification that the SQL Server Agent mail (or Database Mail in SQL Server 2005 and higher) sends. This method is great, because you don’t have to do anything except read the e-mail messages. The downside is that you get one e-mail for each Job step. If you have several plans (each of which each have many Jobs), that can add up to a lot of e-mail. You have to sort through each message to make sure all the steps finished correctly. In my company, we have multiple SQL Servers, some with dozens of plans, and dozens of Jobs. One person simply can’t wade through that much mail, so of course it just doesn’t get done.

Another gotcha with the e-mail method is that the server needs to be able to send you mail. That’s fine for most production servers, but how about testing systems? You don’t want to pollute your production mail system with test accounts, so those servers aren’t good candidates to get checked with the e-mail method. As most developers will tell you, testing servers need maintenance too.

Another disadvantage is that you have to appoint someone who really knows what to look for to check these e-mails. What the server sends is a technical glut of tons of information, even though the only bit you generally care about is whether the job was successful. If it isn’t, that’s when the rest of the information becomes useful.

The second method of Maintenance Plan completion checks involves log files. Again, this is a per-step set of logs with quite a bit of information to sort through to find out whether the step completed successfully. This method has all the disadvantages of the e-mail route, with the added problem of needing to be accessible; you have to log on to the server directly or share out the log location, presenting a security risk. Both SQL Server Management Studio (SQL Server 2005) and Enterprise Manager (SQL Server 2000) contain a log file viewer, and both versions use text files for those logs so that you can read them with other tools as well.

The third way that you can check the Maintenance Plans uses graphical tools as well. In the graphic below, I’ve opened Enterprise Manager and navigated to the Management object and then the Maintenance Plan item.

Figure 1

Right-clicking that item brings up the menu item of Maintenance Plan History.

Figure 2

Once I select that item, you can see the panel shown below:

Figure 3

You can see the various steps and information about them in a grid. My screen resolution forces me to scroll to the right to see the columns that show whether the plan was successful, but you’ll notice that several filters can be applied. I can look at the steps that failed, or the steps that succeeded. While this is useful, to truly be safe I need to see both. I might see a few steps that were successful and think everything is OK when it’s not; or I might see nothing that failed when in fact nothing ran. In addition to those limitations, the data shows all dates — more information than I want to see at one time. I only care about a day or two at a time.

So what I need is a way to see only a certain number of days, and only the columns of data I really care about, from a central location, that just about anyone can check. What I’ll show you in this tutorial kills two birds with one stone. I’ll explain how to solve this problem, and how to use another wizard along the way. This tool is called the Web Assistant, and it creates web pages using a Stored Procedure. In SQL Server 2000 and 2005, you can use a Wizard (a graphical series of panels that asks you questions) to call the Stored Procedure, as I'll describe below. In SQL Server 2008, this feature is gone, but I'll explain what you can do about that in a moment.

The Web Assistant Wizard

Although I’ll show you how to do this using SQL Server 2000 and the graphical tools it has, I’ll explain the stored procedures it is really using under the covers, which you can still access in SQL Server 2005. I’ll start in the Enterprise Management tool from SQL Server 2000. In the panel shown here, I’ve clicked the Tools menu item in Enterprise Manager:

Figure 4

Next, I click Wizards to bring up the screen shown below.

Figure 5

This panel shows the various wizards that SQL Server provides. Notice that I’ve expanded the Management object. The wizard we’re interested in is the Web Assistant Wizard. Clicking that item brings up the following panel:

Figure 6

This panel explains that the wizard will do three things. It will set the source for the data, set the schedule, and set the format. I click Next, and get the following screen:

Figure 7

It’s pretty obvious what’s happening here, but it’s important to note that I can get data from more than one database at a time — I’ll show you how in a moment. For the purposes of this tutorial, I only need data from the msdb database, but if you are going to pull the data from more than one database, just put one of them here.

Once I’ve selected the database, I click Next to see the screen shown in the following graphic:

Figure 8

Here I’m doing two things — I’m naming the job that the wizard will create, and I’m setting the source for the data.

As you can see, the first option is to select columns from tables. This is the simplest source of data, and I’ve not found it to be particularly useful. The exception to that is when another job populates a small table for this output.

The next choice is to have the results of a stored procedure form the source of the data. I like this option since it provides the most flexibility. A stored procedure can display data but can also affect data, with inserts, updates, deletes and so forth. This option along with the next one will allow you to select data from more than one database, by the way. Just structure your statement to fully qualify the server, owner, object name. A stored procedure has the added advantage of running on the SQL Server, so it is an efficient use of resources.

The option I’ve chosen is to enter a SQL statement directly into the wizard. I’ve done this to show you the statement I’ve created, and to encapsulate the tutorial.

I click Next to bring up the following panel:

Figure 9

In this screen I’ve entered a slightly modified form of the SQL Query I showed you for the maintenance plan history output from my last tutorial. Just so you can copy it easily, here it is in text:

USE msdb
GO
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

You can see that I’m using the table that the maintenance plan maintains, and I’m using a query to limit the amount of data it returns. The ORDER BY qualifier places the failed steps at the top — those are the ones I really care about. Keep this script handy — you can use it in the method I’ll show you shortly for the higher versions that don’t have this feature.

Notice in the line directly above the ORDER BY statement I’ve limited the output to the jobs that occurred today. If your plan is arranged differently, then you may need to adjust the number at the end of that statement to include more days. Making this adjustment will also show you more days if you’re not checking daily (shame on you!).

Here’s a tip from experience-land: Enter the query in Query Analyzer first, make sure you get the right results, then copy and paste it in here. While the color-syntax checking works, there’s no preview of the data before you commit the statement, and it’s a bit difficult to edit the statement later.

Once I’m satisfied with the query, I click Next to bring up the screen shown here:

Figure 10

There are a lot of options about the schedule on this panel. The first is just once, right after the wizard runs. This option is fine if you just want one Web page.

The second option is on-demand, meaning that the job will be created, and must be called manually when you want the page created. You normally do this as part of another job or perhaps with a trigger with the sp_runwebtask command.

The third option is to create the page once, but later. Again, not super-useful, at least in this case.

The fourth option is to create the Web page whenever the data changes. Unless you’re making that table I spoke about earlier, to hold the results of another process, I highly recommend you not use this option. While it may seem to make sense, I’ve seen a server buried trying to create the pages when a data change is made 100 times a minute. Just say no.

The final option is the one I use the most. With this choice you set the wizard to use a recurring schedule.

Notice I’ve also selected the option to go ahead and have a page created when I’m done. That’s to show you the output without having to get up at 6:00am in the morning. (No, I wouldn’t really do that.)

After I make my choice I click Next to bring up this screen:

Figure 11

Here I’m setting the schedule, which is set to every morning at 6:00. You can get pretty frequent with this, so again you’ll want to be careful here. Don’t bury your server with this kind of work.

After I set this option I select Next to bring up this panel:

Figure 12

This sets the location of the completed page. I have IIS installed on my SQL Server, so I’ve set the page to go to a root location so that it can be checked.

Once I set the location, I click Next to bring up this panel:

Figure 13

Here I’m given two choices: to allow SQL to help me format the page, or to select a template. I’ll walk you through the SQL assistance. To use a template you set up any normal HTML code to format the page, but for the actual data you set up a place for the data. If you format your page as you wish, then you only have to use the line shown below to accept the output of the data:

<%insert_data_here%>

Of course there’s more to it than that. If you want more formatting of the data, see the references section of the InformIT SQL Server Web site.

For now, let’s continue on and let SQL Server format the page. I click next, and we see the panel shown below:

Figure 14

Here you can see various options for setting the page headings, table titles and data and time stamps. I like the date and time stamp so that I know the wizard ran, just another way to make sure everything is working.

Once I make those selections, I click Next to bring up the screen shown below:

Figure 15

This panel continues the process of setting up the page format. I’ve made my changes and selected Next to bring up this screen:

Figure 16

This is a great option. It allows you to set no hyperlinks, one hyperlink (as I’ve done here), or even a set of hyperlinks, all drawn from a table. That table can be set by yet another application, making this a very extensible process.

After I make my changes, I click Next and you can see the following panel:

Figure 17

Here I’m given the opportunity to parse the data out per page. I can also limit the results at this point. I’m going to leave the defaults here and just click Next. That brings up this screen:

Figure 18

On this panel, the wizard summarizes the actions it will take, and also gives me the opportunity to write the actions out to a SQL script file. The advantage of that is that I can use the script as backup or to port to another server.

Finally, I click the Finish button. Voila! The page is complete. To show you the output, take a look at this Web site:

Figure 19

I see that several steps ran successfully on my server. It’s still important I have a count of how many steps should run during the day. I need to count and make sure that works, or perhaps my query could also return that number and the count of the rows I return. Otherwise, I might gain a false sense of security, believing that I’m in good shape since no 0’s were returned.

This process does a few things for me. I limit the data to just what I care about, I can train someone else to look at the page without the other party having a great deal of knowledge, and I have a central location available to check the plans.

As I mentioned earlier, SQL Server 2005 does not include this Wizard. You should use the built-in Reporting Services tool to create a far more robust report where you can include graphs and so on. However, the stored procedures used to make the pages are still here. You do have to enable them first with these commands:

EXEC sp_configure ’show advanced options’, 1;
GO
RECONFIGURE;
GO
EXEC sp_configure ’Web Assistant Procedures’, 1;
GO
RECONFIGURE
GO

The tables where the maintenance plan history has also changed — for SQL Server 2005 and 2008, you need to use this query:

SELECT 
s.name
, sp.subplan_name 
, spl.start_time AS 'StartTime'
, spl.end_time AS 'EndTime'
, spl.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 
ORDER BY EndTime DESC

Now you can use the following Stored Procedures to create the web page. Check Books Online for the syntax:

sp_makewebtask - Creates the web page creator
 sp_runwebtask - Runs the extract and creation process
 sp_dropwebtask - Removes the web page creator

PowerShell

In SQL Server 2008, Microsoft removed the Web Task wizards and stored procedures. The “official” method to create web pages from SQL Server is to use either Reporting Services or some other application like SharePoint. But I still think that there is a place for creating a web page quickly and simply.

Happily, you can use PowerShell and a built-in command to do just that. If you’re new to PowerShell and SQL Server, I have a whole series of articles starting here that will show you how to get it, install it, and access SQL Server with it. The part that you might not be aware of is the command-let to output results to HTML. It’s called (conveniently enough) ConvertTo-HTML. All you have to do is set up the connection to SQL Server, run the same query I showed earlier, and then pipe all that to the HTML file.

You’ll need to have the SMO libraries installed where you’re running this script (you have that if the client tools are installed) and you’ll need PowerShell of course. Here’s the whole script:

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=MyServerName\MyInstanceName;Database=msdb;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "SELECT s.name, sp.subplan_name, spl.start_time AS 'StartTime', spl.end_time AS 'EndTime', spl.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 ORDER BY EndTime DESC"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] 
$DataSet.Tables[0] | ConvertTo-Html -title "Maintenance History" | Out-File c:\temp\SQLMaintHistory.htm

The important parts are in the connection string, where I’ve specified “MyServerName” and “MyInstanceName.” You need to replace those with the name of your server. Also, the final line, where I send the whole thing to the “temp” drive on my hard drive, needs to be changed to what you want.

InformIT Articles and Sample Chapters

Are you new to web pages in general? We have a great starting point for you with What You Need to Get Started Creating Web Pages.

Books and eBooks

My friend Ed Wilson has a great book on PowerShell: Microsoft Windows PowerShell Step by Step (Safari Books Online). You can do a lot more with it than I’ve shown here.

Online Resources

There's another article on the stored procedures I’ve mentioned here.