I’ve talked about illogical errors before. In several answers on Database Administrators (one, two, three), I show how you can use a
TRY_CONVERTto work around an error where a non-numeric value, that should have been filtered out by a join or other clause, still leads to a conversion error. Erland Sommarskog raised a Connect item over a decade ago, still unaddressed, called “SQL Server should not raise illogical errors.”
Recently we had a scenario where a query was failing on one server but not another. But this was slightly different; there were no numerics involved. Imagine this scenario: a source table has a column that is
varchar(20). A query creates a table variable with a column that is
varchar(10), and inserts rows from the source table, with a filter in place that only exposes values that are 10 characters or less.
In a lot of cases, of course, this scenario is perfectly fine, and everything works as expected.
Read the whole thing. There is a method to the madness, and Aaron explains how it can come up in some cases but not others.