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.
About the author: Brian Cryer is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.