Microsoft KB Archive/888494

= 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 =

Article ID: 888494

Article Last Modified on 11/17/2004

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-





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 



STATUS
This behavior is by design.



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 After you run the script, you receive the error message that is mentioned in the &quot;Symptoms&quot; section.

Additional query words: SQL2000

Keywords: kbtshoot kbprb KB888494

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.