UNION ALL, TEXT field and ORDER BY error

Question

Hi,

Im having two tables with attributes like date(datetime),headline(varchar),text(text)

Now i want to UNION ALL these two tables and sort by the datetime. When doing this i'm getting the error:

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.

After trying back and forth i found out that it is the text attribute which is causing the error. But what to do? I tried casting to VARCHAR with no succes. Both tables uses text format in the text attribute.

Also when removing the ORDER BY it all works fine. What to do?

The original SQL query is below, but you can just reply to the simplified above.

SELECT     id, datetime, author, headline, intro, text, type, toppriority,
           secondpriority, comments, companyid, '1' source 
FROM     Table1
UNION ALL
SELECT     AutoID AS id, Dato AS datetime,
           ID COLLATE SQL_Latin1_General_CP1_CI_AS AS author, NULL AS headline, 
           NULL AS intro, Notat COLLATE SQL_Latin1_General_CP1_CI_AS AS text,
           CAST(NotatTypeID AS VARCHAR) AS type,
           NULL AS toppriority, NULL AS secondpriority, NULL AS comments,
           Selskabsnummer AS companyid, '2' source 
FROM     Table2
WHERE     (NotatTypeID = '5') OR (NotatTypeID = '6')
ORDER BY datetime DESC

Thanks in advance

Answer 1

One way round this is to run the union as a sup query and order the results afterwards:

SELECT * FROM 
(
    SELECT     id, datetime, author, headline, intro, text, TYPE, toppriority,
            secondpriority, comments, companyid, '1' source 
    FROM     Table1
    UNION ALL
    SELECT     AutoID AS id, Dato AS datetime,
            ID COLLATE SQL_Latin1_General_CP1_CI_AS AS author, NULL AS headline, 
            NULL AS intro, Notat COLLATE SQL_Latin1_General_CP1_CI_AS AS text,
            CAST(NotatTypeID AS VARCHAR) AS TYPE,
            NULL AS toppriority, NULL AS secondpriority, NULL AS comments,
            Selskabsnummer AS companyid, '2' source 
    FROM     Table2
    WHERE     (NotatTypeID = '5') OR (NotatTypeID = '6')
) a
ORDER BY datetime DESC

Answer 2

What about casting the datetime field to some text field in the index? Please note that using 'datetime' and 'text' as field/alias names can be quite confusing, and a source for potential problems.