SQL Server Static page export Technology 1

server| Static | Page This article extracts from the Railway publishing house new "use BackOffice to establish Intranet/extranet application" (now in Haidian Book city has been sold). This book details how to use the Microsoft BackOffice family of products to build intranet/extranet applications. With it you will master the installation and setup of NT, use IIS to set up a Web site, set up a network conferencing system through ILS, create an enterprise mail and collaboration system with Exchange, build Web database applications with SQL Server, and use proxy Server establishes secure connections to the Internet, creates network TV/radio stations with Media server, builds powerful chat rooms with Chart server, builds personalized mailing lists with Site server, and analyzes access to Web sites, Use Commerce Server to establish business-to-business or business-to-consumer e-commerce sites. In addition, the book also discusses the security of the network, which guides you through a more robust and secure network application. After reading this book, you will find that the implementation of the rich and colorful network applications is so simple ...
Absolutely original, welcome to reprint. But make sure you keep the above text.



We have a preliminary introduction to the static page export technology, and now we have some examples to complete the detailed explanation of it.
Static page exports can be accomplished by invoking the storage process and using wizards in Enterprise Manager. Wizards make it easy to export data from a database to a static page or to establish an export task. It is convenient to use, but its flexibility is not as good as the method of invoking the storage process. Let's begin by introducing how to create a static page export from the wizard.
6.9.4.1. Using Enterprise Manager for static page export
Let's say we're going to create a static page that contains the title of all the articles in the daily newspaper and their layout. The content of this page requires that each time the data in the database changes, it can change. Let's look at the specific implementation process:
Locate the server in Enterprise Manager for which you want to make a static page export, open its management container, and right-click the Web publishing object. Select the new Web Assistant job option from the pop-up shortcut menu. The wizard that will start the static page export (pictured below).

Figure 6.9.4.1-1 Static page Export Wizard Step 1, prompting information
As you can see, this wizard is divided into three steps: Defining export data, scheduling export tasks, and formatting Web pages. Click Next to continue.
Then we will select which database we want to export the data from, and after we choose Test, click Next to continue (as shown below).

Figure 6.9.4.1-2 Static page Export Wizard step 2, select Export Database
We will then be asked to enter the name of this export task (the default is Test Web Page) and select how to export the data (as shown below).

Figure 6.9.4.1-3 Static page Export Wizard Step 3, select Export method
There are three ways to choose between these two options:
Data from the tables a columns that iselect: Select the fields you want to export directly from the table.
Result set (s) of a stored procedure iselect: The exported data comes from the operating results of a storage process.
Data from the Transact-SQL statement ispecify: The export is derived from a section of SQL statements.
According to our application requirements, after selecting the third item, click Next to continue.
Then, depending on the way we choose to export, we will require a section of SQL statement to be entered (as shown below).

Figure 6.9.4.1-4 Static page Export Wizard Step 4, enter the export query statement
We enter the following SQL statement:
Select ID as ' number ', Banmian as ' layout ', Timu as ' title '
From Gaojian
Where kanwu= ' publications ' and DatePart (Yy,riqi) =datepart (Yy,getdate ()) and
DatePart (Dy,riqi) =datepart (Dy,getdate ())
When the input is complete, click Next to continue.
We will then select the schedule for this task (pictured below).

Figure 6.9.4.1-5 Static page Export Wizard step 5, set the export task
There are several options to choose from:
Only one times when icomplete this wizard: executed when the wizard is complete and only once.
On demand: Execute when needed.
Only one moment at: executes only once at a specified time.
When the SQL Server data changes: Executes when the data in the database changes.
At regularly scheduled intervals: perform this task periodically.
The lower part of the dialog box has a generate a Web page when the wizard is completed selection box that indicates whether to perform a page export (by default) When this wizard process completes.
Depending on our needs, you should choose when the SQL Server data changes. Click Next to continue.
The next step will vary depending on the choice of the previous step. What we see will be the dialog box shown in the following illustration:

Figure 6.9.4.1-6 Static page Export Wizard Step 6, specify the trace field
The wizard will ask us to choose which of the fields in which table to track. Once these fields in the table have changed (when inserting, deleting, and modifying records), this task will be run to update the contents of the static page. We chose to track the IDs, Riqi, Laiyuan, and Timu four fields in the Gaojian table. When the selection is complete, click Next to continue.
We will then select the location and filename of the static page (pictured below). We can place it in the same directory as a virtual directory in IIS. This allows users to access them through a Web page.

Figure 6.9.4.1-7 Static page Export Wizard Step 7, specify a location for static file storage
After entering the appropriate storage directory and filename, click Next to continue.
The format and encoding of the exported page will then be set (figure below).

Figure 6.9.4.1-8 Static page Export Wizard Step 8, specify the format and encoding of the static file
For page formats, we can have the system automatically complete its format, or you can format it by specifying a template file. The following sections discuss the template file. Here we now choose to automatically complete the format of the page by the system work. You can choose the character set of the page in the use Character Set Drop-down list box, because we are using Chinese, so we should choose Simplified Chinese (GB2312). Click Next to continue.
Next you will enter the title of the page title and the SQL statement Execution result table and select the font size for it (figure below).

Figure 6.9.4.1-9 Static page Export Wizard step 9, specify a static page title
The Apply a time and data stamp to the Web page selection box at the bottom of the dialog box is used to determine whether the page generated dates and times are displayed in the page, allowing the user to know when the data in this page is generated. When the settings are complete, click Next to continue.
The result table executed by the SQL statement is then set (as shown in the following figure).

Figure 6.9.4.1-10 Static page Export Wizard step 10, static page display effect settings
where Yes,display column names and no,display data only are used to indicate whether the result table contains field names (shown in the header). Depending on our needs, select the Yes,display column names checkbox to display the field name. Some of the options below are used to set some font effects in the table. You can select fixed-width fonts (fixed), proportional-width fonts (proportional), plus-black (bold) and italic (italic) four. The draw border Lines around the HTML table selection box is used to indicate whether the table box is drawn for the result table. When the settings are complete, click Next to continue.
You will then be prompted to include a hyperlink in the page (pictured below).

Figure 6.9.4.1-11 Static page Export Wizard step 11, set the page hyperlink
The following three options are available:
No: Do not include hyperlinks in the page. We select this item.
Yes,add One hyperlink: Add a hyperlink. and indicate the connection and display information for it.
Yes,add a list of hyperlink URLs: Add a hyperlink listing. This list is obtained from a table in the database through a section of SQL statements.
When you select the first option, click Next to continue.
Next we can limit the number of records returned and the number of results returned per page (figure below).

Figure 6.9.4.1-12 Static page Export Wizard step 12, return the result quantity limit
We can select the "No,return All rows of data" radio box, not limit the total number of records returned, or select "Yes,return" The radio box to limit the number of records returned (by entering the maximum number of records to return in the text entry box that follows).
Selecting the No,put all in one svrolling page radio box means that all returned records will be placed in a single paging file. If you choose the "Yes,link the successive pages together" radio box, you can place the returned records in many paging files, reducing the length of the resulting page. It is easy for users to browse. You can enter the following text entry box to enter the maximum number of records that each page file contains. We chose each page to contain up to 20 records. When the settings are complete, click Next to continue.
The wizard then prompts us to complete the setup of the static page export task and displays some relevant information about it (as shown below).

Figure 6.9.4.1-13 Static page Export Wizard step 13, complete the confirmation information
If you click the Write Transact-SQL to File button, you can save the task as an SQL language script file. This can be done in the future by running this script to create this task again. or modify the content of the script to make some adjustments to its implementation. According to our settings, the resulting SQL statement is as follows:
EXECUTE sp_makewebtask @outputfile = N ' D:\test\cbb\ttt.htm ',
@query =n ' Select Id,banmian,timu
From Gaojian where kanwu= ' publishes ' and
DatePart (Yy,riqi) =datepart (Yy,getdate ())
and datepart (Dy,riqi) =datepart (Dy,getdate ())
Order by Banmian ',
@fixedfont = 1,
@colheaders = 0,
@HTMLheader = 3,
@webpagetitle =n ' Microsoft SQL Server Web Assistant ',
@resultstitle =n ' Query Results ',
@dbname =n ' Test ',
@whentype = 10,
@nrowsperpage = 20,
@datachg =n ' Table=gaojian Column=id,timu,laiyuan,riqi ',
@procname =n ' Test Web Page ',
@codepage = 936,
@charset =n ' gb2312 '
Once you have completed the static page Export task through the wizard, you can no longer modify its properties through the Enterprise Manager. Only SQL statements can be used to modify their settings. We will then explain the meaning of each of the parameters in the following sp_makewebtask.
Click the Finish button to finish setting up this task. The resulting page of results is shown in the following figure.

Figure 6.9.4.1-14 Static page export Results
Note: After you set up this static page export task, you will receive an error message if you modify, add, or delete the section. If you turn off IE's display-friendly HTTP error message feature (it is recommended that you turn off this feature when debugging an IDC application so that you can see the detailed error message returned by the system), you will see the following error message:
Run Query Error
[state=42000] [error=229] [Microsoft] [ODBC SQL Server Driver] [SQL Server] [Microsoft] [ODBC SQL Server Driver] [SQL Server] EXECUTE permission denied on object ' test Web Page ', database ' test ', owner ' dbo '.
[state=37000] [error=16805] [Microsoft] [ODBC SQL Server Driver] [SQL Server] SQL Web assistant:could not execute the SQL statement.
This is due to the fact that the defined static page export run rights are not granted to each user by default. So when you perform IDC as a editer, you will receive an error message that permissions are not granted. However, this does not affect the operation of the manuscript data.
There are two ways to solve this problem, one is to modify the corresponding IDC file to insert and modify the manuscript, and change the username and password to the SA and SA password respectively. This will run the IDC file as an SA, so that no more errors will occur. This approach can create some security risks if someone reads these IDC files. He knows the password of the SA so that he can log in to the database as an SA. There are almost unlimited operations and modifications to the database. To avoid this, you can grant the Editers role execute permissions to the static page export task by running the following SQL statement:
Grant all on test.dbo. [Test Web Page] to Editers
Where test Web page exports the name of the task for this static page, the reason why it is expanded with brackets is that its name contains spaces. In SQL Server, objects with spaces in their names should be enclosed in brackets when referencing their names. And for these objects, information such as their permissions will not be modified in Enterprise Manager. It can only be implemented through SQL statements. This is due to a bug in Enterprise Manager that is still not available in the SP3 of SQL Server.
Although it is a simple process to establish a static page export task through the Wizards in Enterprise Manager, it is somewhat less applicable for some complex situations. For example, let's say we're going to put the daily publication content on the Internet and publish it through a static page. This is not enough to rely on the functionality provided in the Wizard of Enterprise Manager. This relatively complex task can be achieved only by invoking the sp_makewebtask storage process and using some techniques.