Have you ever encountered an error in SQL Server that clearly indicates where the problem is, but doesn’t provide a clear solution? If so, you’re not alone. In this article, we will explore one such error and discuss multiple solutions to help you resolve it.

Let’s start by recreating the scenario and understanding the problem. Run the following script:

USE Tempdb
GO

CREATE TABLE TestTable (
    ID INT,
    MyText NTEXT
)
GO

SELECT ID, MyText
FROM TestTable
WHERE MyText = 'AnyText'
GO

DROP TABLE TestTable
GO

When you run the above script, you will encounter the following error:

Msg 402, Level 16, State 1, Line 1
The data types ntext and varchar are incompatible in the equal to operator.

At first glance, you might think that the data types are compatible with the equal to operator. However, the error message suggests otherwise. The issue lies in comparing ntext with varchar using the equal sign.

To resolve this error, we need to change the data type on the right side of the string to nvarchar from varchar. We can do this by adding an ‘N’ before the string. Modify the script as follows:

USE Tempdb
GO

CREATE TABLE TestTable (
    ID INT,
    MyText NTEXT
)
GO

SELECT ID, MyText
FROM TestTable
WHERE MyText = N'AnyText'
GO

DROP TABLE TestTable
GO

When you run the modified script, you will encounter the following error:

Msg 402, Level 16, State 1, Line 1
The data types ntext and nvarchar are incompatible in the equal to operator.

The error message now suggests that we are comparing ntext with nvarchar. Now that we understand the error properly, let’s explore various solutions to this problem.

Solution 1: Convert the data types to match using CONVERT function

We can change the data type of MyText to nvarchar and use the CONVERT function to match the data types. Modify the script as follows:

SELECT ID, MyText
FROM TestTable
WHERE CONVERT(NVARCHAR(MAX), MyText) = N'AnyText'
GO

Solution 2: Convert the data type of columns

If you want a permanent solution, you can change the data type of the columns from NTEXT to NVARCHAR(MAX). For example:

ALTER TABLE TestTable
ALTER COLUMN MyText NVARCHAR(MAX)
GO

After making this change, you can run the original query again, and it will work fine.

Solution 3: Using LIKE command instead of equal to command

If the above solutions are not feasible, you can use the LIKE command instead of the equal to command. Modify the script as follows:

SELECT ID, MyText
FROM TestTable
WHERE MyText LIKE 'AnyText'
GO

Any of the three solutions mentioned above will work. However, it is recommended to change the column data type from ntext or text to nvarchar or varchar if possible, as the ntext and text data types are marked as deprecated. It’s always a good idea to update deprecated data types early on to avoid any issues in the future.

If you are unable to convert the original column, Solution 1 can be used as a temporary fix. Solution 3 should be considered as a last resort.

If you have any other methods to resolve this error, please let me know, and I will add them to the original blog post with due credit.