SQL Server Error: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator

When you google this error you will get a lots of solution. But no one help me to resolve my problem. So i am working on this issue to find out my solution & finally i got a simple problem which i want to share with you. My situation is i have a link server (SQL Server 2008) with my working Sql server 2005. I have created a table in the link server means Sql server 2008 which is given below:

2008table

And i wrote a sample stored procedure with a dynamic SQL to produce this error like below:

CREATE PROCEDURE SP_Test
@Table_Tail AS VARCHAR(20)
AS
BEGIN
 SET NOCOUNT ON;
 DECLARE @STRSQL AS VARCHAR(5000)

 SET @STRSQL = 'DELETE FROM [xxxxx\SQL01].[RA_CTL_SUMMARY].dbo.TBL_IBSPhase2 WHERE EntryDate='''+@Table_Tail+''' 
 AND Prefix=''NOKIA'' '
 EXEC (@STRSQL) 

END

The problem is when i want to run or execute this query i will get the below error:
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.

Solution:

In this case the solution is simple. Just change the datatype size of column Prefix MAX to a fixed size will resolved my problem. Means in my scenario i have declared the datatype of Prefix column from VARCHAR(MAX) to VARCHAR(5000).

In your case this may not be the situation so keep googling & try other solutions. This is one of the solution only which i did not get from google.

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code