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
Best Answer
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:
As for possible solutions, try changing the
CAST
toTRY_CAST
- but note that this may lead to results that you don't expect, becauseTRY_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 onmaterial='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 theWHERE
clause, which prevents SQL Server from using an index properly. TheTRY_CAST
won't solve this, it only takes away the actual conversion error. For more on this, check out "sargable" expressions.