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.
The equivalent on my PC appears as shown in Figure 4.
Figure 4: Navigating to the Enterprise Manager
3. Select Enterprise Manager from the menu. The Enterprise Manager console opens.
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
7. Expand the Databases folder, so as to display its contents.
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
The Database Properties dialog appears, defaulted to the General tab.
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
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).
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
15. Select Query Analyzer from the menu. The Connect to SQL Server dialog appears.
16. Select the appropriate server in the SQL Server selector box.