Printer Friendly Version

This month, I want to continue to build out the intranet site we've started in our previous articles. A standard item in every intranet is a staff/employee list. Most companies implementing Windows SharePoint Services (WSS) are large enough to have a database that contains all employees. We will use the aggregation features of SharePoint to include data from this database into our intranet.

Last month, we discussed customizing the site home page using the built-in Web Parts. That customization was performed using only a Web browser. This month, we will also use FrontPage 2003 to build a page that searches a database table. For this article, the database we will use is the Northwind sample database that is included in Microsoft's SQL Server.

On our Web page, we will display the information pertinent to calling a fellow employee from your desk: name, telephone extension, and country. While Northwind has only a few employees, we want to provide the ability to search by first and/or last name.

Discuss this article or find answers to your SharePoint questions in the Intranet Journal Discussion Forum.

Database Setup

Before we can include the database content on our intranet, we need to ensure that the Web server can access the database. Since an employee database usually contains data that should not be publicly available, we need to provide a way to read basic information without compromising the sensitive data. A complete discussion of database security is beyond the scope of this article, but we will do some steps to provide a basic level of protection.

The default configuration of a WSS site is Integrated Security. This means that each Web request runs in the context of the user account requesting the page (the end-user). While this is fine for most tasks, not every user account will have access to the employee database. To provide the connection from the Web server to the database, we will create a SQL Server user account. This account will have access only to the Northwind database and will only be allowed to execute a specific stored procedure to perform the staff search.

Stored Procedure
To perform the search of the database, we will create a stored procedure. In the Northwind database, we will search the Employees table, selecting only the columns required. The results are ordered alphabetically, as you would expect.

CREATE PROCEDURE StaffSearch ( @LastName nvarchar(20), @FirstName nvarchar(10) ) AS SET @LastName = RTRIM(@LastName) + '%' SET @FirstName = RTRIM(@FirstName) + '%' SELECT LastName, FirstName, Extension, Country FROM Employees WHERE LastName LIKE @LastName AND FirstName LIKE @FirstName ORDER BY LastName, FirstName

Database User Account
The following commands will create an SQL User account, and allow that account to connect to the Northwind database. Then, the account is granted permission to execute the stored procedure.

EXEC sp_addlogin 'spuser', 'passwd', 'Northwind' USE Northwind EXEC sp_adduser 'spuser' GRANT EXEC ON StaffSearch TO spuser

Web Server Setup

Data Retrieval Services
WSS uses a feature called Data Retrieval Services to access data in external databases (databases other than the configuration and content databases). By default, the Data Retrieval service will not allow access to stored procedures. This setting is changed via the Central Administration page:

• On the Web server, click Start | Administrative Tools | SharePoint Central Administration.
• Click "Configure data retrieval service settings" (at the very bottom).
• Make sure that the "Enable these data retrieval services" box is checked.
• Make sure that the "Enable update query support" box is checked.
• Click OK

Document Library for Web Part Pages
Since WSS stores the content of the site in a database, we cannot just create an HTML page and put it on the server. To add pages to a site, they must be stored in a document library. WSS comes with a default "Shared Documents" library, but this library is configured to store MS Word documents. We will create a new document library to contain all Web pages we might need in our intranet:

• From the home page, click Create in the top Navigation Bar
• In the Create Page, click Document Library
• On the New Document Library page, complete the form as detailed below. Most important, the document template must be Web Part Page (which is the last choice in the list).
  • Name: Web Pages
  • Navigation: Yes
  • Document Versions: No
  • Document Template: Web Part Page
  • Click Create
• On the New Web Part Page form, complete the form as detailed below
  • Name: PhoneSearch
  • Choose a Layout Template: Header, Left Column, Body
  • Click Create

The new page will be displayed in design mode.

At this point, we are finished with the set-up tasks for the database and the Web server.

Page 2: Building the Web Page