Microsoft KB Archive/269426

= PRB: Use of FULL OUTER JOIN with TEXT Column Through a Linked Server Returns Error Message 8626 =

Article ID: 269426

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q269426



SYMPTOMS
When all of the following conditions are met:
 * A remote table is being joined with a FULL OUTER JOIN.
 * The remote table has a TEXT column.
 * The TEXT column is referenced in the select list.

the following error message occurs:

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.



CAUSE
This behavior is by design because FULL OUTER JOINs do not allow you to access a TEXT column through a linked server.



WORKAROUND
Use a UNION ALL statement with a LEFT OUTER JOIN and RIGHT OUTER JOIN. The following example may be helpful: SELECT * FROM s LEFT OUTER JOIN linked_server1.pubs.dbo.t as t on s.i= t.i UNION ALL SELECT * FROM s RIGHT OUTER JOIN linked_server1.pubs.dbo.t as t on s.i= t.i If you want to eliminate duplicate rows, use a combination of LEFT and RIGHT OUTER JOINs with a UNION statement and you can use the system defined function SUBSTRING. For example: SELECT s.i, s.j, SUBSTRING(s.t, 1,200), t.i, t.j, SUBSTRING(t.t, 1, 200) FROM s LEFT OUTER JOIN linked_server1.pubs.dbo.t as t on s.i= t.i UNION SELECT s.i, s.j, SUBSTRING(s.t, 1,200), t.i, t.j, SUBSTRING(t.t, 1, 200) FROM s RIGHT OUTER JOIN linked_server1.pubs.dbo.t as t on s.i= t.i For information about how the tables and linked servers in the preceding queries are set up, refer to the &quot;More Information&quot; section of this article.



MORE INFORMATION
You can use the following steps to reproduce the problem:

NOTE: To successfully reproduce the problem, the linked server name and login should be changed to match your situation.   On a remote server named linkedserver1, run the following script: --on remote server use pubs go IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[t]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE t GO

CREATE TABLE t(i int, j int, t text) GO INSERT INTO t (i, j, t) VALUES (1, 2, 'this is a test') GO   On local server, run the follow script: --on local server use pubs go

IF EXISTS(SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[s]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) DROP TABLE s GO CREATE TABLE s(i int, j int, t text) GO INSERT INTO s (i, j, t) VALUES (1, 2, 'this is a test') GO IF EXISTS (SELECT * FROM master.dbo.sysservers WHERE srvname='linked_server1' AND srvid<>0) EXEC sp_dropserver @server='linked_server1', @droplogins='droplogins' GO EXEC sp_addlinkedserver @server='linked_server1' GO EXEC sp_addlinkedsrvlogin @rmtsrvname='linked_server1', @useself='false', @rmtuser='sa', @rmtpassword='' GO   Run the following query, which involves a full outer join to reproduce the problem: SELECT * FROM s FULL OUTER JOIN linked_server1.pubs.dbo.t as t on s.i=t.i                   

Additional query words: Error 8626

Keywords: kbprb KB269426

-

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

© Microsoft Corporation. All rights reserved.