You may receive an 8626 error message when you run a query that contains a UNION ALL operator between two tables in SQL Server 2000 (888494)

SYMPTOMS

When you run a query that contains a UNION ALL operator between two tables in Microsoft SQL Server 2000, you may receive the following error message:

Server: Msg 8626, Level 16, State 1, Line 1
Only text pointers are allowed in work tables, never text, ntext, or image columns. The query processor produced a query plan that required a text, ntext, or image column in a work table.

This problem occurs when the following conditions are true:

The query contains an ORDER BY clause.
Two columns of the same rank in the SELECT clause have different data types.
One of columns that have different data types has a binary large object (BLOB) data type, including text, ntext, or image.

CAUSE

This problem occurs when you query the two tables, and the binary large object column is cast to the binary large object data type. Therefore, the binary large object data type does not go through the sort that is required to implement the query plan.

WORKAROUND

To work around this problem, use one of the following methods:

Use the same column types in both tables.
Cast the binary large object column in the query to the other column data type.

Modify the query to retrieve the columns in a case statement. For example, the following code uses a case statement:

-- Original query

SELECT Table2.id id, Table2.data data FROM Table2 
UNION ALL 
SELECT Table1.id, Table1.data FROM Table1 
ORDER BY id 

-- Modified query

SELECT id, case sid when 1 then data1 else data2 end as data
FROM
(SELECT Table1.id, 1 as Sid, Table1.data as data1, NULL as data2 FROM Table1
UNION ALL 
SELECT Table2.id id, 2 as Sid, NULL, Table2.data FROM Table2) Q
ORDER BY id

MORE INFORMATION

Steps to reproduce the problem

To reproduce this problem, run the following Transact-SQL script:

CREATE TABLE [Table1] ( [id] [int] NOT NULL , [data] [nvarchar] (50)) 

CREATE TABLE [Table2] ( [id] [int] NOT NULL , [data] [ntext]) 

SELECT Table2.id id, Table2.data  data  FROM Table2 
UNION ALL 
SELECT Table1.id, Table1.data FROM Table1 
ORDER BY id