Optimizing Microsoft SQL Server 2000 Reporting Services: Execution Log Reporting: Preparation as a Data Source
Create a Database to Contain the Execution Log
Our next step is to create a database to contain the information from the Execution Log. As we have discussed, Reporting Services creates a log to house this information already – a log that we might simply access, and from which we might report, without the need for an “intermediate” database. The information that we find in the default database leaves a little to be desired from the perspective of formatting, ease of reporting and so forth. We will therefore take a gift as it is presented, and use the tools provided to create a database and user-friendly schema to make our job easier. We can, after all, modify the database as required in the future, should the need arise, based upon client feedback or our own inclinations.
To begin the construction of our reporting data source, we will first create a new database from the MSSQL Server Enterprise Manager, and then create the constituent tables of the database using the Query Analyzer. This step will be facilitated by the table creation script that resides among the components that we placed into the folder we created above.
1. Click Start.
2. Navigate to the Microsoft MSSQL Server Enterprise Manager shortcut in the Programs group, as appropriate.
3. Select Enterprise Manager from the menu. The Enterprise Manager console opens. 7. Expand the Databases folder, so as to display its contents. The Database Properties dialog appears, defaulted to the General tab. 11. Adjust the settings on the Data Files and Transaction Log tabs to place the files in the file system location you desire (I left mine at default for this exercise). 15. Select Query Analyzer from the menu. The Connect to SQL Server dialog appears.
The equivalent on my PC appears as shown in Figure 4.
Figure 4: Navigating to the Enterprise Manager
4. Expand Microsoft SQL Servers by clicking the “+” sign to its immediate left, as required.
5. Expand SQL Server Group.
6. Expand the appropriate server.
The Enterprise Manager console appears as depicted in Figure 5.
Figure 5: Enterprise Manager (Compressed) View
8. Right-click the Databases folder.
9. Click New Database … in the context menu that appears, as shown in Figure 6.
Figure 6: Select New Database
10. Type the following into the Name box:
SSP-RS001_ExecutionLog
The General tab of the Database Properties dialog appears as depicted in Figure 7.
Figure 7: The Database Properties Dialog – General Tab
12. Click OK to accept changes and to close the Database Properties dialog.
The dialog closes and the database appears (after refreshing the Enterprise Manager console, as required).
13. Leaving Enterprise Manager open, click the Start button once again.
14. Navigate to the Microsoft MSSQL Server – Query Analyzer shortcut in the Programs group, as appropriate.
The equivalent on my PC appears as shown in Figure 8.
Figure 8: Navigating to the Query Analyzer
16. Select the appropriate server in the SQL Server selector box.
No comments yet... Be the first to leave a reply!