Query fails from application but runs fine in SSMS

Question

I have a query that runs fine when executed from SSMS but when done so from the application, it fails with a (database level) exception.

select ksprintid
from kshistory_akt h
where cast(h.serialno as decimal(28)) >= 1
    and h.sku = '11105031'

The application error is

System.Data.SqlClient.SqlException: Error converting data type varchar to numeric.
   bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   bei System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   bei System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   bei System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   bei System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   bei System.Data.SqlClient.SqlDataReader.Read()
   bei NHibernate.Driver.NHybridDataReader.Read()
   bei NHibernate.Loader.Loader.DoQuery(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
   bei NHibernate.Loader.Loader.DoQueryAndInitializeNonLazyCollections(ISessionImplementor session, QueryParameters queryParameters, Boolean returnProxies, IResultTransformer forcedResultTransformer)
   bei NHibernate.Loader.Loader.DoList(ISessionImplementor session, QueryParameters queryParameters, IResultTransformer forcedResultTransformer)

The code that executes it:

ISessionFactory _factory = new Configuration().Configure().BuildSessionFactory();
CurrentSessionContext.Bind(_factory.OpenSession());
IList printedJobs = _factory.GetCurrentSession()
    .CreateSQLQuery("select 1 from kshistory_akt h where cast(h.serialno as decimal(28)) >= :start and h.sku = :material")
    .SetString("material", "11105031")
    .SetParameter<int>("start", 1)
    .List();

But, the weird thing is that the result set of

select ksprintid
from kshistory_akt h
where h.sku = '11105031'

(without the actual cast) is empty:

So - how can an empty result cause an conversion error? And why doesn't it happen from within SSMS?

And to top it off: This only happens on one of three servers where this application runs. Every other server happily runs the command from the application without any errors (as it should be, because empty results should not cause problems when converting, right?)

SSMS Connection options

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
ARITHABORT
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

Client Connection options

ANSI_WARNINGS
ANSI_PADDING
ANSI_NULLS
QUOTED_IDENTIFIER
ANSI_NULL_DFLT_ON
CONCAT_NULL_YIELDS_NULL

Answer 1

Your WHERE clause contains an explicit datatype conversion, which is terrible for performance, and can also cause this type of problem.

This query will probably show you what's wrong:

select serialno AS notVeryDecimalSerialNo
from kshistory_akt h
where try_cast(h.serialno as decimal(28)) IS NULL;

As for possible solutions, try changing the CAST to TRY_CAST - but note that this may lead to results that you don't expect, because TRY_CAST returns NULL instead of a conversion error.

If I were to guess, your problem is that there is a serialno which won't convert to decimal, but that value is not found if you filter on material='11105031'. So if SQL Server filters the material first, your query succeeds, otherwise the conversion fails. For more details, here's a blog post I wrote on the subject.

The performance problem that I mentioned is caused by the CAST in the WHERE clause, which prevents SQL Server from using an index properly. The TRY_CAST won't solve this, it only takes away the actual conversion error. For more on this, check out "sargable" expressions.