PRB: Misleading Error Message with an IN Clause On Text Data Type (271525)


The information in this article applies to:

  • Microsoft SQL Server 7.0
This article was previously published under Q271525

SYMPTOMS

SQL Server returns a misleading error message when you use the IN clause with the text data type.

The error message that occurs is:
Server: Msg 306, Level 16, State 1, Line 1 The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.

CAUSE

In SQL Server 7.0, the sysmessages system table in the master database has the following text:
Server: Msg 306, Level 16, State 1, Line 1 The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.
in the sysmessages.description column for error 306.

WORKAROUND

This problem has been corrected in Microsoft SQL Server 2000.

MORE INFORMATION

In SQL Server Query Analyzer, perform the following steps:
  1. Create a table named test with a text column in the PUBS database:
    Use PUBS
go
create table test (col1 text)
go
  2. Select the data from the text column in the table "test" that you just created:
    select * from test where col1 in ('aaa')
go
The code returns the following:
Msg 306, Level 16, State 1, Server XYZ, Line 1 The text, ntext, and image data types cannot be used in the WHERE, HAVING, or ON clause, except with the LIKE or IS NULL predicates.
In SQL Server 7.0, the ON in the error message should be IN.

In SQL Server 2000, the following error message is returned, as expected:
Server: Msg 306, Level 16, State 1, Line 1 The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.
Modification Type:MajorLast Reviewed:12/9/2000
Keywords:kbCodeSnippet kbprb KB271525
©2002 Microsoft Corporation. All rights reserved.