Symptoms

After installing or configuring adTempus to use a SQL Server database, the adTempus service fails to start. Error messages in the diagnostic logs may indicate that adTempus is looking for non-existent tables or columns. For example:

Error -2147217865: Cannot alter table 'wmitrigger' because this table does not exist in database 'adtempus_XXXX'. (source=Microsoft OLE DB Provider for SQL Server; native error=4902; SQL state=42S02)

Error -2147217865: Invalid object name 'environmentVariables'. (source=Microsoft OLE DB Provider for SQL Server; native error=208; SQL state=42S02)

Alternatively, adTempus may start, but you may encounter "Unexpected Error" messages when you try to modify jobs and other objects.

Cause

The adTempus software assumes that the database is case-insensitive. If the database uses case-sensitive collation, database queries executed by adTempus fail when they use different capitalization for object names than the database schema does.

The adTempus database creation script should ensure that the adTempus database uses the proper collation, but does not.

Workaround

SQL Server 7 and Earlier

If you are using SQL Server 7 or earlier, the collation is defined for the entire SQL Server instance and cannot be changed for a specific database. If your instance uses case-sensitive collation, you will not be able to use adTempus with this SQL Server instance. You will need to use another SQL Server instance, if available, or install and use MSDE instead.

SQL Server 2000 and Later

In SQL Server 2000 and later you can change the collation used for the adTempus database, making it case-insensitive. To do so, execute the following query on the database server:

ALTER DATABASE DatabaseName COLLATE SQL_Latin1_General_CP1_CI_AS

Replace DatabaseName with the name of the adTempus database.

Status

This issue was resolved in adTempus version 3.0, which supports only SQL Server 2000 or later.