solutions products evaluate support partners about
solutions
products
evaluate
support
Online Training
Evaluation Center
Tech Notes - 7.x
Tech Notes - 6.x
File Downloads
File Uploads
Request for Support
partners
about
Installing Express Reports for Microsoft SQL Server
Technical Note 1389
Last Reviewed 14-Dec-2000

Applies To
Express Software Manager version 4.5 or higher
Microsoft SQL Server version 6.5 and higher

Summary
This technical note offers an overview of Express Reports for Microsoft SQL Server and provides installation instructions.

If you are using Express Software Manager version 4.5, Express Metrix strongly recommends that you obtain and apply a patch, Ex1283ag.exe, to update two script files that shipped this version. This patch is not necessary for Express Software Manager version 5.0. Where possible, Express Metrix recommends applying the patch before installing Express Reports for SQL Server. For more information about obtaining the patch, see Technical Note 1390.

About Express Reports for Microsoft SQL Server
Express Reports for Microsoft SQL Server are similar to the Express Reports that ship with Express Software Manager 4.x and higher, but they add the capability to store and process the data on Microsoft SQL Server 6.5 and above.

By using Express Reports for Microsoft SQL Server, you can

  • Improve speed and robustness

  • Reduce network bandwidth

  • Integrate the data with Web reports, SMS, or other products storing data in a Microsoft SQL Server database

Architectural Overview
Express Reports for Microsoft SQL Server includes three components: Microsoft SQL Server, Microsoft Access, and the communications link that connects these two databases.

Microsoft SQL Server
Microsoft SQL Server provides the back end for Express Reports for SQL Server:

  • Data is stored in Microsoft SQL Server.

  • Processing occurs on the SQL server with local data.

Microsoft Access
Microsoft Access 97 provides the front end for Express Reports for SQL Server:

  • Fully featured user interface

  • Report formatting

  • Easy interface for customizing reports

  • Free run-time license ships

Communications Link
ODBC drivers provide the communications link between the front end and the back end.

Figure 1 shows the relationship between the components of this product. Access must run on the reports machine. This is typically the administrator's PC with Express Console installed. SQL Server typically runs on a powerful machine that easily processes the data to create reports. ODBC links the Access database and the SQL Server database. (You must configure an ODBC Data Source on the reports machine to point to the SQL Server database. This is discussed in detail later.)

Figure 1. Relationship between the three components: the SQL Server, the reports machine, and the ODBC link.
Figure 1. Relationship between the three components: the SQL Server, the reports machine, and the ODBC link.

Design Advantages
There are several advantages to creating reports this way:

  • SQL Server can handle larger data sets than Access.

  • SQL Server is less prone to data errors. All actions are logged to a log file so that the current state can always be recovered.

  • SQL Server often resides on a powerful machine.

  • There is less network traffic because all processing is done on the server.

Internally, these new reports have almost the same architecture as the Access reports. The difference is that the processing is on the server rather than on the Reports machine, which is likely to be less powerful.

Figure 2. SQL Server Architecture
Figure 2. SQL Server Architecture

Installation
Installation generally requires the use of two different computers: the Microsoft SQL Server machine and the Reports machine. The following steps include the machine designation in brackets. Although the SQL Server and Reports machines are usually different, it is possible to use a single computer.

Note: Ideally the Reports machine is the same machine where the Express Console is located. This is not a requirement, but it makes installation easier.

Overview of Installation
This is an overview of the installation process. Details for each step are outlined below.

  1. Install Microsoft SQL Server version 6.5 with Service Pack 3 or above.

  2. Create a database on the SQL Server.

  3. On the Reports machine, create an ODBC Data Source to connect to the database on the SQL Server.

  4. Migrate the Express Knowledgebase using the Express KB Installer.

  5. Create the required tables using the Express Database Installer.

  6. Export the data using Express Console or Express Library Manager Service.

  7. Launch Express Reports for SQL Server.

Note: Although not mandatory, it is recommended that Steps 4, 5, and 6 be followed in this order. The script Mssql.scp (used in step 5) creates the tables with indexes that have been optimized for reporting. The export process (step 6), which creates some of the required tables if they do not already exist, does not create these same indexes. Although at the completion of the export process, the tables are re-optimized for reporting, performance may be affected if the sequence is not followed.

Step 1. Install Microsoft SQL Server <SQL Server Machine>
Express Reports for SQL Server was tested on Microsoft SQL Server version 6.5 (Microsoft SQL Server Service Pack 3) and above. Earlier versions or service packs are not supported.

Install SQL Server following the prompts during the installation process. Your configured sort order and case sensitivity will not adversely affect Express Reports with SQL Server, unless you are working with data that was exported prior to Express Software Manager 4.0.1. (See step 6, below.)

If you are using SQL Server version 6.5, configure the following settings. (Note: SQL Server version 7.0 automatically handles these settings.)

  • In Enterprise Manager on the Configuration tab for the SQL Server, configure the following:

    • Configure SQL Server to use an amount of memory appropriate for the amount of physical RAM on the machine. (If you do not do this step, SQL Server may default to using only 16 MB of RAM, regardless of the RAM available on the machine. As a result, performance may suffer.) Consult your SQL Server documentation to determine what the appropriate setting is. The following Microsoft support web site offers recommendations for SQL Server 6.5 users:

      http://support.microsoft.com/support/kb/articles/Q110/9/83.asp

      Note: You may have to create a profile before accessing this link.

    • Increase the number of locks by 10,000 (beyond what you need for your other databases).

    • Increase the number of open objects by 1000 (beyond what you need for your other databases).

    These settings changes will take effect after the SQL Server is restarted.

  • Increase the size of the Master database by 10MB (beyond what you are already using for your other databases.)

  • Increase the size of the Temp database by 50MB (beyond what you are already using for your other databases.)

Step 2. Create a Database <SQL Server Machine>
Create a database on the SQL Server. At large sites, a database administrator will probably handle this task. The steps for this process vary depending on the version of SQL Server. For additional information, see your SQL Server documentation.

SQL Server version 6.5 -- If you are using SQL Server version 6.5, before you can create a database, you must allocate physical space in the form of a database device. Express Metrix recommends that the SQL Server database contain approximately 40 MB of free space for the knowledgebase, and one and one-third (1.3) MB for each machine in the Express Library.

Use SQL Enterprise Manager to create two database devices, one for the data and one for the log file. For the data device, allocate approximately 1 MB per machine in the Express Library and 40 MB for the knowledgebase. For the log device, allocate approximately one third the size of the data device. (For example, if you have an Express Library with 300 machines in it, and you will be exporting that library to SQL Server, allocate approximately 340 MB for the data device and approximately 100 MB for the log device.) Note: The log size requirements dramatically increase if Truncate Log on Checkpoint is not selected. For more information about this option, see the "Configure Permissions and Options" section below.

After creating the database devices, create the database. Enter the names of the data and log database devices created above.

SQL Server version 7.0 -- Use SQL Enterprise Manager to create a database. Note that database devices do not exist in version 7.0. By default, SQL Server version 7.0 automatically handles the allocation of physical space for the database. Refer to the physical space recommendations for SQL Server version 6.5 (above) if your SQL Server version 7.0 is not set up to automatically handle the allocation of physical space.

Configure Permissions and Options
Once the database is created, use Enterprise Manager to configure permissions and options.

  • On the Permissions tab, set permissions appropriate for your environment. Create privileges for Table, View and Rule are required for installation.

  • On the Options tab, Express Metrix recommends selecting Truncate Log on Checkpoint. By selecting this option, you can avoid filling up the log file, but you lose the ability to automatically recover your data. None of the calculations performed by the reports needs to be recovered, but the data itself will not be protected if this option is set.

    If you select this option, be sure to schedule regular database backups so you can recover your data if needed.

    If this option is not set, then the log size requirements will be very large. (The log size could potentially exceed the size of the data device depending on how often database and log dumps are performed, or how often reports are run.) If you do not select Truncate Log on Checkpoint, you will need significantly more space for the log device, and an experienced SQL administrator should closely monitor the log usage to determine how often database and log dumps should be performed in your environment.

Step 3. Create an ODBC Data Source <Reports Machine>
An ODBC Data Source (DSN) is a named connection from a machine to a remote database. There are two types of data sources, system and user. A system DSN is available to all users on system, including services. A user DSN is only available to the user who created it. If you are using an Express Service, you should create a system DSN.

To create a data source:

  1. Use the ODBC Control Panel applet (or ODBC32 on some systems) to create a data source.

  2. Select the SQL Server driver. Use SQL Server driver version 2.6 or higher.

  3. Name the DSN.

  4. Depending on your driver, specify the remaining fields, including:

    • The machine name of the SQL Server machine.

    • The security model your SQL Server uses, standard or integrated (some drivers).

    • Specify the database name created in Step 2.

    • Accept remaining defaults.

Note: If SQL Server drivers are not installed on the machine, you must install them. You can download drivers from the Microsoft web site.

Step 4. Migrate the Knowledgebase Using the Express KB Installer <Reports Machine>
The Express KB Installer (Kbinst.exe) creates the knowledgebase tables and moves the knowledgebase data from the knowledgebase file (Express.kb) on the Express Console into the database. It uses an ODBC Data Source and the path to an Express Knowledgebase, to copy the data from the knowledgebase into the SQL Server database indicated by the Data Source. The KB installer will overwrite any existing knowledgebase information in the SQL Server database. Note: You must have Create permissions in the target database to install the knowledgebase and stored procedures.

To run the KB Installer: Go to the Start Menu and highlight Programs, then Express Software Manager, then Resource Toolkit, then click Express KB Installer. The KB installer takes a long time (approximately 20 minutes) to accomplish its task.

Kbinst.log is a log file created by the KB installer and is useful for troubleshooting. This activity log is located in the SQL Reporting subdirectory under <install path> (by default, C:\Program Files\WRQ\Express Console\Reskit\SQL Reporting).

Note: You must run the KB Installer every time you receive a knowledgebase update.

Step 5. Create Required Tables Using the Express Database Installer <Reports Machine>
The Express Database Installer (Dbinst.exe) installs the views and stored procedures required by Express Reports for Microsoft SQL Server into the database created in Step 2. The Database Installer uses an ODBC Data Source and a special script (Mssql.scp)--it runs the script on the database indicated by the data source.

Note: If you are using Express Software Manager version 4.5, Express Metrix strongly recommends that you update the script file, Mssql.scp, that shipped with this version of Express Software Manager prior to running the Express Database Installer. (This patch is not necessary for users of Express Software Manager version 5.0.) For more information about the updated script, and obtaining and applying the patch, see Technical Note 1390.

  1. Run the Express Database Installer. (From the Start Menu, highlight Programs, then Express Software Manager, then Resource Toolkit, then click Express Database Installer.)

  2. Enter the name of the ODBC data source created in Step 3.

  3. Specify the script Mssql.scp, located in the SQL Reporting folder under <install path> (by default, C:\Program Files\WRQ\Express Console\Reskit\SQL Reporting). The script is a text file, with server-specific SQL commands embedded between the tags <SQL> and </SQL>. Dbinst.log is a log file created by the database installer, and is useful for troubleshooting.

Step 6. Export Data Using Express Console or Express Library Manager <Any Machine>
CAUTION: Before you upgrade, turn off OpenGL screen savers on all Express Console and Express Enterprise Services machines. (Some 3D OpenGL screen savers consume the majority of the CPU cycles, causing Express performance to slow down dramatically.)

To Export Data: Open the library to be exported in Express Console. Click Library Properties on the File menu. On the Export tab, enter the Data source, User ID, and Password. From Express Console, click Export on the File menu to export the data from the selected Express libraries into the SQL Server database you created in Step 2.

Note: Express Metrix recommends that the Express Library Manager service be used to regularly export the data to SQL Server rather than the Express Console. The Library Manager service allows you to schedule the export to regularly occur at off peak hours. For more information about using the Express Library Manager service, see Chapter 30 in the Express Software Manager System Administrator Guide.

If you have previously exported data using Express Software Manager version 4.0, note the following:

  • If the existing SQL Server is case sensitive (by default, SQL Server is not case sensitive), then Express Metrix recommends re-exporting the data to a new database--particularly if the "Discard metering data after export" option is not enabled. See step 2 above to create a database.

Step 7. Launch Express Reports for Microsoft SQL Server <Reports Machine>
Note: The reports require a full or runtime version of Microsoft Access 97.

On the Reporting menu, click SQL Reports. When you start up reports, you are prompted to select an ODBC Data Source. Choose the Data Source where you exported the Express Software Manager library. Note: The first time you run reports, you are asked to provide a Data source name, Login, and Password to connect to the Data Source. You can change this information anytime after that on the ODBC Configuration tab in the Options dialog box in Express Reports for Microsoft SQL Server.

From Express Reports for Microsoft SQL Server, view reports as you would normally. The data is from your ODBC-compliant database.

  • Unlike Access reports, there is only one "Expodbc.mdb" file. Therefore, customization need be done only once.

  • There is a potential security risk with Access. Concerned administrators should use an account with appropriate privileges.

  • Only one user at a time should make changes in Reports. If another user attempts to run reports when the database is already open, he will receive a message box indicating the database is locked. If the user then chooses to unlock the database, the database remains unlocked for all users (that is, they will not receive the warning message) until every user who had it open when it was originally unlocked has shut down his or her session.

Related Technical Notes
1390 Obtaining the SQL and Web Reporting Patch, Ex1283ag.exe
9989 Express Software Manager Technical Notes