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:
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.
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.