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


The information in this article applies to:

  • Microsoft SQL Server 2000 (all editions)
  • Microsoft SQL Server 7.0
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 "More Information" 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.
  1. 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
  2. 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
  3. 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
Modification Type:MajorLast Reviewed:10/17/2003
Keywords:kbprb KB269426
©2003 Microsoft Corporation. All rights reserved.