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 &gt; 0 then
            substring(i.message, charindex('&lt;LegalEntity&gt;',i.message)+13, charindex('&lt;/LegalEntity&gt;',i.message)-charindex('&lt;LegalEntity&gt;',i.message)-13)
         else '' end as LegalEntity
    from (select *, charindex('&lt;LegalEntity&gt;', 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 Support Services with the dump produced in the log directory.
03/13/2013 10:30:24,Server,Unknown,Error: 17310&lt;c/&gt; Severity: 20&lt;c/&gt; State: 1.
03/13/2013 10:30:24,spid101,Unknown,External dump process return code 0x20000001.&lt;nl/&gt;External dump process returned no errors.
...
03/13/2013 10:30:20,spid101,Unknown,* BEGIN STACK DUMP:
03/13/2013 10:30:20,spid101,Unknown,*
03/13/2013 10:30:20,spid101,Unknown,* *******************************************************************************
03/13/2013 10:30:20,spid101,Unknown,SqlDumpExceptionHandler: Process 101 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
03/13/2013 10:30:20,spid101,Unknown,***Stack Dump being sent to C:\Program Files\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\SQLDump0039.txt
03/13/2013 10:30:20,spid101,Unknown,Using 'dbghelp.dll' version '4.0.5'


Does anyone have any idea as to WHY this occurs?
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.