Get More out of SQL Server's Web Assistant Wizard

In my previous article, Publish Dynamic Data with SQL Server's Web Assistant, you learned that you don't need any HTML skills to share your database data on the Internet.

The Wizard can take care of all the formatting and lead you through the creation of a Web page step by step. In addition, you can point SQL Server's Web Assistant Wizard at the tables that contain the data you want to retrieve or monitor. Thus, query skills are not required either.

Indeed, one of the key benefits of the Web Assistant Wizard is that you can publish database data online without any Web development or query building expertise. The Wizard is straightforward enough to assist even the beginning programmer. But as we'll see in this article, HTML and SQL skills come in pretty handy when you need to use the more advanced options of the Wizard.

In this article, I will explain two advanced options of the Web Assistant Wizard:

1. tweak the HTML output


2. tweak the SQL queries

Why Use Advanced Options?

At first glance, the Web Assistant Wizard can take care of all your work. So, why would anyone want to use its advanced options?

The Web Assistant Wizard automatically can create a simply laid-out HTML page with simple data. And that's where its capabilities stop. In theory, this might be fine, but in the real world you'll discover the need to use either a different layout, or a certain subset of data, in your application. For instance, you may only want to use the data from orders that were handled by certain employees in a certain period. Through the Wizard, you can delve deeper into the standard layout and table queries that the tool can produce.

Tweak the HTML Output

The Wizard's default options will produce an HTML page that looks something like this:

The Wizard allows you to change some settings. We'll assume for sake of example that you've followed the first six steps in my original article, Publish Dynamic Data with SQL Server's Web Assistant Wizard.

Through this process, we examined the prerequisites, the data to publish, the scheduling mechanism and the changes the Wizard should monitor. In Step 7 of that article, we observed the HTML page that SQL Server should generate to reflect your data.

In Step 7, the following screen marks the beginning of the layout configuration:

I will discuss the template function later on in this article, so for now choose, "Yes, help me format the Web page". You can leave the character set option to its default, unless you're using nonstandard characters such as Chinese or Eastern European. When you click Next, you'll reach the title layout option.

1. Define Title Layout

   

   If you fill in the text as shown in the example above, on your Web page, the words "Northwind Employees" will appear in your browser's title bar. The title text leading the table will contain the word, "Employees". Despite the fact I don't like big letters, we decide here that the table title should be "H1 - Largest".

2. Format the Table

   

   SQL Server will publish the data in a table. In this screen, you can decide if you want to show the column names or not, whether you'd like to add extra font options to the table text (the possibilities consist of: fixed or proportional, bold, italic) and if whether you'd like to show a border around each table cell.

   In this example, I chose to alter all default options.

3. Add Hyperlinks

   

   This step is quite out of place in this process. It actually has nothing to do with changing the layout of the data, but enables you to add one or more hyperlinks to the page. Most of the time, you will add a link to your corporation's home page at this point.

   It is possible to add multiple links, but in all honesty, the solution Microsoft has provided for this is really far-fetched. You'll have to predefine and fill a separate table, which you can access in the bottom field with a query that should look something resembling "SELECT label, url FROM hyperlinks".

   In this case, I just filled in the SitePoint URL and a label description, and clicked Next.

4. Limit Rows

   

   Database tables can consist of thousands of rows. Usually the last thing you'll want is to publish endlessly scrolling Web pages with tons of information. In this step, the Web Assistant Wizard provides you with the possibility to break the data into multiple pages. Additionally, the Wizard will automatically add a hyperlink that links to the next page.

5. Check the Result

   This is the last step. Click Finish on the confirmation page to let the Wizard generate the HTML file(s). If you navigate to the file, you'll see the following result:

   

Here, you can see all the changes we've made:

• the text "Northwind Employees" in the browser's title bar
• the large "Employees" title text above the table
• the first five rows presented in a proportional, bold, italic font
• the link to SitePoint
• a reference to the next rows