ISNULL on NTEXT causes fatal exception on SQL Server

I have a weird error that occurs on a client's SQL Server 2005 box. We have provided them with a dataview that is populated with a query with a CTE with several joins / subqueries that does some text value interpretation on an NTEXT field that contains an XML blurb

I know that we could have used used other field types and / or methods, but it is what it is for good reason that I hope I would not have to explain here.

The problem is that for some reason, this query creates a fatal exception on the SQL Server.

Herewith a condensed version of the business end of the actual T-SQL that causes the error

declare @@dtDateStart datetime, @@dtDateEnd datetime
set @@dtDateStart = getdate()
Set @@dtDateEnd = dateadd(year, 1, @@dtDateStart)
;with  qItems
    (date_created, SYSTEM_ID, LegalEntity) as
(   select i.date_created,
        i.externalKey as SYSTEM_ID,
        case when i.HasLegalEntity > 0 then
            substring(i.message, charindex('',i.message)+13, charindex('',i.message)-charindex('',i.message)-13)
         else '' end as LegalEntity
    from (select *, charindex('', message) as HasLegalEntity from dbo.INT_Error_Queue) as i
    where i.date_created between @@dtDateStart and @@dtDateEnd)
select 
    QItems.date_created, 
    QItems.SYSTEM_ID, 
    Qitems.LegalEntity
from QItems
WHERE
(isnull(Qitems.LegalEntity, '') not in (select key_value from Special_app_Settings where key_name = 'EntityName'))

At first I suspected that it has something to do with the where clause, because the [key_value] on [Special_app_Settings] is of type nvarchar(4000), and [i].[message] on [INT_Error_Queue] is of type [ntext], but I am not sure whether that would cause the error I have. If I remove the where clause, the error does not occur.

Herewith the dump from the SQL Server Log:

```
03/13/2013 10:30:24,Server,Unknown,A user request from the session with SPID 101 generated a fatal exception. SQL Server is terminating this session. Contact Product Suppo

I think you might be a victim to problem I have seen before, introduced with Cumulative update for Service Pack 2. Is the compatibility level of your database 80 (SQL 2000)?

See Microsoft KB 938102 for Hotfix details, but I suggest you upgrade to the latest service pack (or definitely a later cumulative service pack).

Essentially the Query Engine is evaluating your where clause and creating joins using non-ANSI operators such as *=, you then fall foul of this Sql bug. The problem occurs if the following conditions are true:

• The query references a view that uses non-ANSI outer join operators,

For example, the view uses the = operator or the = operator.

• The column list of the SELECT statement in the view contains a

subquery.

• The version of SQL Server 2005 is SQL Server 2005 SP2 or a later

version.

• You set the database of SQL Server 2005 to use compatibility level

80.

StackExchange Database Administrators Q#37588, answer score: 2