- What’s Going On? Using SQL Server Web Wizard to Display Maintenance Results
- The History Channel
- The Web Assistant Wizard
- Summary
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
The Web Assistant Wizard
I'll start the Web Assistant Wizard by opening Enterprise Manager and then select Tools and Wizards..., as shown in Figure 2.
){kind=link}
Figure 2. Starting the wizard selection
That brings up the wizard selection screen, as shown in Figure 3.
){kind=link}
Figure 3. Wizard selection screen.
Notice that I expanded the Management selection and then highlighted Web Assistant Wizard. After I select OK, the wizard begins, as shown in Figure 4.
){kind=link}
Figure 4. Introductory screen
There's not much to see here, so I select the Next button to bring up the screen shown in Figure 5.
){kind=link}
Figure 5. Database selection screen
In this panel, I selected the msdb database, which has the information I'm after. After I select Next, the screen shown in Figure 6 appears.
){kind=link}
Figure 6. Naming the plan
In this panel, I'm doing a couple of things. I'm naming the job and then I'm selecting the location of the data. Data can come directly from a table, from a stored procedure (which has the capability to write data to the database as well as read it) or from a SQL statement. I want to do the latter, so I punch the last box and then Next. That brings up the screen shown in Figure 7.
){kind=link}
Figure 7. Entering the SQL statements
Here, I'm entering the query that I developed earlier. Although you may not be able to see it, the panel uses the syntax coloring found in Query Analyzer, making it easier to ensure that the syntax is correct. After I've verified the statement, I select Next to bring up the screen shown in Figure 8.
){kind=link}
Figure 8. Selecting the scheduling
This panel shows the flexibility of this wizard. Although I'm showing a demonstration of maintenance plan history, you can use this wizard to display many kinds of data: once, when the job is called; one time in the future, whenever the data changes (be careful selecting that option or you'll overburden the server with web-page creations); or periodically. I'll select the "periodically" option, and also pick the option to make the Web page just after the wizard finishes. I then select Next, which brings up the screen shown in Figure 9.
){kind=link}
Figure 9. Setting the interval
Because I selected a recurring interval, this panel sets the frequency of that interval. There's a lot of granularity here to make the page as fresh as necessary. I request the page creation each morning at 6:00 a.m., when I'm sure my maintenance plans have run. I then select Next to continue, bringing up the screen shown in Figure 10.
){kind=link}
Figure 10. Setting the output location
This panel sets the location for the Web page. This can be a standard drive or share location, but it's often more useful to publish this under a Web directory, so that I can access the page from the road, if necessary. I've set mine to be on the root Web site of this server. After making that change, I click Next to bring up the screen shown in Figure 11.
){kind=link}
Figure 11. Formatting the Web output
Next, the wizard asks me whether I have a standard template that I use for formatting the page, or whether I want to encode that directly in the job. I'll allow SQL to format the page for me, but it's worth a look in Books Online to create a template, if you will use this wizard for more than a few things. After I select Next, I'm shown the screen shown in Figure 12.
){kind=link}
Figure 12. Setting the titles
Here, the Web Assistant Wizard sets a title for the Web page (shown at the top of the Web browser) and the title for the table with the data. Notice that the date and time of the page creation will be applied to the page, which helps to ensure not only that the maintenance plan runs, but also that the page is recent. After I make those changes, I select Next to bring up the screen shown in Figure 13.
){kind=link}
Figure 13. Formatting the table
The results from the query will be placed in an HTML table, so here I set parameters such as whether the column names display and the type of font used for the results. I also choose a border for the table.
Figure 14. Adding hyperlinks
){kind=link}
I can also add other links to the page, even ones I select from a SQL table. I don't really need that for this particular application, so I select Next here to continue and bring up the screen shown in Figure 15.
){kind=link}
Figure 15. Setting the row display
Another useful feature of this wizard is that it allows you to select how the rows from the data will be displayed. This application doesn't normally contain a large set of rows (no more than 1000), but if your application does, you may want to choose to limit the results that return or to place them on separate HTML pages, which are linked with an arrow for continuation at the bottom. I then select Next to continue, bringing up the screen shown in Figure 16.
){kind=link}
Figure 16. Completing the wizard
This panel indicates the end of the wizard, and here I can press Back to make changes, press Finish to complete the process, or write the results of the job creation to a file. I'll just press Finish, and I'm shown the screen shown in Figure 17.
){kind=link}
Figure 17. Ending the wizard
And here you might get a nasty surprise—if you ran the wizard from an Enterprise Manager separate from the server. I found that I needed the same service pack version on both systems to make this a reality, but with that completed, everything worked correctly.
Now that the wizard is complete, the Web page is ready for access from a browser. I dial in the URL from the server, and sure enough the Web page shows up; results are in date order descending. The key now is to check the page, as shown in Figure 18, making sure the dates and activities are accurate.
){kind=link}
Figure 18. The completed Web page