Cry about...
MS-Windows Troubleshooting

ERROR [42000] [Microsoft] [SQL Native Client] [SQL Server] The data types varchar(max) and text are incompatible in the equal to operator

Symptom:

When trying to update a record in a database table, the following exception is thrown:

ERROR [42000] [Microsoft][SQL Native Client][SQL Server]The data types varchar(max) and text are incompatible in the equal to operator.

Possible Cause and Remedy

• I encountered this error when working with a varchar(max) field, and attempting to update the field from 4052 characters to 4068 characters. I suspect that the SQL Native Client driver has a buffer limit and generates this error if its buffer is overflowed - presumably somewhere between the two numbers mentioned (be aware that this is speculation on my part as a buffer limits is not something I can prove.)

  Possible remedies:

  • Identify whether the contents of the field should grow to the size it is. If not then you may have an application error or issue.

  The above turned out to be my problem. So the following are what I would have tried next:

  • Consider using a different driver from the SQL Native Client one, or checking whether a later version is available.

These notes have been tested within Microsoft Visual Studio .NET 2008 running under Windows Vista Business against SQL Server 2008, and may apply to other versions as well.