Ambiguous column name 'IS_ACTIVE' in Configuration Tool


When clicking Create/Upgrade Database button in the Configuration Tool, you get a popup like the one below:

The text error reads:

Database Error: Database error - Ambiguous column name 'IS_ACTIVE'.
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.

Error is occurring with Configuration Tool 4.2 or above.

This is a frequent problem that we have been getting in Support these days. 


This error occurs when you are either using SQL Server 2000 or a database catalog in a version newer than 2000 but the database catalog is set with Compatibility Level 80 (SQL Server 2000).

Agile Platform 4.2 and above do not support the use of SQL Server 2000 or the use of database catalog with a Compatibility Level set for SQL Server 2000 (80).


if you are running the database catalog in a SQL Server 2000 instance, you will need to move the catalog to a SQL Server 2005 (or any of the supported versions of SQL Server for the particular version you are running) or simply upgrade the instance to the newer version.

If the catalog is deployed in a newer SQL Server, you will need to change the compatibility level to that of the SQL Server you are running: if SQL Server 2005, then Compatibility Level must be 90; if SQL Server 2008, then Compatibility Level must be 100.

More information

In SQL Server 2005 and above, if you have two tables, and a column with the same name in both tables, you can select only one of the columns from one of the table, and use it a ORDER BY clause. E.g. myColumn exists on both table1 and table2:

select table1.myColumn, table1.Name, table2.Value
from table1, table2
order by myColumn

In SQL Server 2000 the above statement is invalid and will give an error similar to the one in this post.