Optimizing Microsoft SQL Server Reporting Services: Execution Log Reporting: Preparation as a Data Source

Hands-On Procedure

Before we can work with the Execution Log data, we need to take several preparatory steps to make the data it contains useful from a reporting perspective. Our preparation will include executing a DTS package that Reporting Services provides to extract the data from the Execution Log, and to put it into a table structure that we can easily query. As we noted earlier, the internal table in the report server database does not present the data in a format that is readily user-friendly. The DTS package included with Reporting Services resolves this problem by collecting all of the data we need and putting it into a structure that is intuitive in its organization.

As part of our setup, we will be creating folders, copying files, creating a database, and performing other actions. To complete the procedure you will need to have the appropriate access and privileges, at the MSSQL Server 2000 level, within Reporting Services, MSSQL Server Analysis Manager, and elsewhere, to perform the respective actions. Setup is a one-time event, unless we decide to recreate the table elsewhere, or to otherwise modify the simple procedures to accommodate local requirements, and so forth.

As we have done in previous articles, we will make copies of the existing components we use, so as to preserve the originals in a pristine condition, in case we wish to perform (or are already performing)  

Preparation

Create a Folder and Populate it with the Components We Will Need

To prepare for our session with the Execution Log, we will create a folder to house the DTS package to which we have referred earlier, along with other files that we will need to complete our practice exercise. Working with copies of the files will mean that we can comfortably proceed in a practice environment without concern about “returning things to the way they were,” should we later wish to perform similar exercises in our development or production environments (where it would obviously be appropriate to maintain proper source control over the respective objects). The folder can be created anywhere on the PC that is convenient from a local perspective.

1. Right-click Start.

2. Select Explore to launch Windows Explorer.

3. Navigate to a convenient place to create the folder that will contain the “collateral” for our practice session.

4. Create a folder named as follows:

SSP-RS001

The folder appears similar to that depicted in Figure 1.

Figure 1: New Folder in Place to House Lesson Components

5. Navigate to the following folder (or it’s equivalent, if your installation differs from default):

C:\Program Files\Microsoft SQL Server\80\Tools\Reporting Services\ExecutionLog

The folder should contain the following four files:

• cleanup.sql

• createtables.sql

• rsexecutionlog_update.dts

• rsexecutionlog_update.ini

6. Using SHIFT+Click, select all four files noted above.

7. Right-click the highlighted selection.

8. Select Copy from the context menu that appears, as shown in Figure 2.

Figure 2: Select Copy from the Context Menu