BillSum Table Rebuild

The BillSum table is a snapshot of all billing information per Timekeeper and Matter. It is effectively a non-live amalgamation of the Timecard, Costcard and Chargecard tables. It is updated periodically but can become out of date and inaccurate due to various errors in the system.

To rebuild the BillSum table take the following steps, courtesy of Weronika Kozlowska, Application Support Consultant at Elite Thomson Reuters UK:

1. Go to Notification task Manager > select the BillsumMsgQueue task > tick the 'Disable' option and release

2. Go to the Build Billsum Tables process > tick all 4 month / period options > Background > Release

3. After a while, go to Notification Task and Log viewer and click the 'View Background Tasks' button you will see your process initally with a 'Running' status > periodically check whether the status changes to 'Complete' (It might take a few hours)

4. Once the task is complete, go back to Notification task Manager > select the BillsumMsgQueue task un-tick the 'Disable' option and release.

If you have a period of the day/week when no billing information (time, costs, charges) is ever captured, late at night or early morning for example, the Build Billsum Tables can be scheduled to run automatically via the Notification Task Manager process. The BillsumMsgQueue task need not be disabled first if you are confident no billing information will be captured during the run time. Note that the Build Billsum Tables task can take several hours to run.

Time Out During Upgrade - Toggle READ_COMMITTED_SNAPSHOT Failed

Utilities 3e can sometimes fail when running the Upgrade process and throws a time out error. The Upgrade process then hangs and doesn't respond to the stop command/button. The only way out is to close the whole Utilities app.

The error message has a comment of "Toggle READ_COMMITTED_SNAPSHOT Failed" with extra info along the lines of "Error executing System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."

The reason for this is the database has its READ_COMMITTED_SNAPSHOT setting turned OFF before upgrading and does not turn it back ON before failing. Utilities 3e fails to function correctly, as does the IDE and the front end!

The database can become inaccessible in Sql Query Analyser / SSMS, preventing queries being ran against it. Taking the database offline then bringing it back online solves this problem.

What's required is to turn READ_COMMIT_SNAPSHOT back on. This is typically done by running this on the DB in question:

ALTER DATABASE <DATABASE_NAME> SET READ_COMMITTED_SNAPSHOT ON

However, this statement can fail to run correctly and endlessly run in Query Analyser, never completing. A better option (for Sql Server 2005 - i.e. 3e 2.5 and below) is:

IF(CHARINDEX('Microsoft SQL Server 2005',@@VERSION) > 0)

BEGIN

    DECLARE @sql VARCHAR(8000)

    SELECT @sql = '

    ALTER DATABASE ' + DB_NAME() + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

    ALTER DATABASE ' + DB_NAME() + ' SET READ_COMMITTED_SNAPSHOT ON;

    ALTER DATABASE ' + DB_NAME() + ' SET MULTI_USER;'

    EXEC(@sql)

END

An IIS Reset may be needed after this, but after that all 3e systems should be running as before. The Update from within Utilities3e always seems to work the second time around.