Optimizing Microsoft SQL Server 2000 Reporting Services: Execution Log Reporting: Preparation as a Data Source
17. Select the radio button to the left of the appropriate authentication selection, supplying login credentials as required.
The Connect to SQL Server dialog appears (with my settings for this exercise) as depicted in Figure 9.
Figure 9: Connect to SQL Server Dialog
18. Click the OK button to log in to SQL Server via Query Analyzer. The Connecting … message briefly appears, and then the Query Analyzer console opens.
19. Select SSP-RS001_ExecutionLog in the Database Selector atop the console.
20. Select File —> Open from the main menu.
21. Navigate to the folder we created above, SSP-RS001, in the Open Query File dialog that appears.
22. Select the following file within the SSP-RS001 folder:
The Open Query File dialog appears similar to that shown in Figure 10.
Figure 10: Selecting the Table Creation Script
23. Click Open on the Open Query File dialog to open the script.
The table creation script opens, and appears in the Editor pane of the Query window. This script, executed within the new SSP-RS001_ExecutionLog database, will create the tables that we will target for the Execution Log data in later steps.
24. Click the Execute Query button on the toolbar, as depicted (circled) in Figure 11.
Figure 11: Executing the Query
NOTE: Had we named the database anything other than SSP-RS001_ExecutionLog, or if we were working with any but the local instance of MSSQL Server, we would have needed to edit rsexecutionlog_update.ini for the respective Execution Log database (referenced as “[Destination]”), and / or Server identification, etc. Modifications can be accomplished by accessing rsexecutionlog_update.ini (another of the files we copied into the SSP-RS001 folder above), using Notepad or any other text editor, as we shall see later in our exercises.
The script is executed, and we are informed of its successful completion, along with the various events that have transpired, in the Messages pane of the Query window, as shown in Figure 12.
Figure 12: Indication of Successful Processing
At this point we can verify creation of the tables by shifting back to Enterprise Manager, expanding the SSP-RS001_ExecutionLog database we created earlier, and clicking on Tables. Our tables appear as depicted in Figure 13.
Figure 13: Manual Verification of Creation of Tables (Optional)
25. Select File —> Exit to close Query Analyzer.
Extract Execution Log Data, and Populate the New Reporting Database
We will return to Enterprise Manager at this point, where we will extract the data existing in the Execution Log. To achieve our objective, we will use another tool, a DTS package, provided with Reporting Services in the files that we copied into our SSP-RS001 folder earlier.
We will begin by returning to Enterprise Manager.
12. Return to the Enterprise Manager console we left open earlier.
13. Right-click Data Transformation Services.