Microsoft KB Archive/271525

= PRB: Misleading Error Message with an IN Clause On Text Data Type =

Article ID: 271525

Article Last Modified on 12/9/2000

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



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:   Create a table named test with a text column in the PUBS database: Use PUBS go create table test (col1 text) go   Select the data from the text column in the table &quot;test&quot; 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.

Additional query words: 306 error message

Keywords: kbcodesnippet kbprb KB271525

-

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

© Microsoft Corporation. All rights reserved.