0

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:

Empty result set

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
  • Do the different servers have the same data & indexes? Or different? serialno may still be evaluated regardless of that sku value returning 0 rows. – Gareth Lyons Apr 10 '17 at 12:27
  • Also, check your connection flags in the client and SSMS. – Daniel Hutmacher Apr 10 '17 at 12:34
  • @GarethLyons Not the same data, the production server causes the problem, the rest don't. They are replicated occasionally from production, so their data is definitely "older". Indices should be the same across all of them, is there a way I can reliably "dump" the whole table config (with indices, config options, etc) to make sure? – Florian Peschka Apr 10 '17 at 12:40
  • Please show your c# code. – McNets Apr 10 '17 at 12:56
  • @DanielHutmacher I added the options (I think you meant these?) - The only difference seems to be ARITHABORT but I already tried switching that OFF in SSMS, to no avail. – Florian Peschka Apr 10 '17 at 12:58
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.

  • I know that there are numbers in that column that cannot be converted, but none that also match 11105031 in the other column. I also noticed that when I turn around the filters, the query works. My problem is that I don't know why one server seems to filter serialno first (and runs in to the problem), but all others seem to filter material first – Florian Peschka Apr 10 '17 at 13:26
  • 1
    That is the point. It's nearly impossible to tell SQL Server specifically which column/expression to filter first. And every server could have a different cached query plan, which is probably what you're seeing. For this to work, you'll need to fix the query or this problem will just keep coming back. – Daniel Hutmacher Apr 10 '17 at 13:31
  • Okay, I see. But why does turning the conditions around work? If the server doesn't care for the order I give the conditions to him, why does the behaviour change when I re-arrange them? I'm sorry if those are naive questions, I just want to learn how to avoid this in the future. For now, you gave me the tools to handle the problem at hand - but what's the gist I take away from this? Don't assume conditions will follow any order at all (which seems what you're saying)? – Florian Peschka Apr 10 '17 at 13:35
  • 1
    It's not about the order, it's about where the calculation happens (on which side of the equal sign, if you will). So this works: WHERE a=CAST(123 AS varchar(10)), this won't: WHERE CAST(a AS int)=123, because, potentially, not all values of a are valid int values, but 123 can always be converted to varchar. – Daniel Hutmacher Apr 10 '17 at 13:38
  • Gotcha. The code actually does try to "catch" the problem beforehand by checking if the sku contains serialnos that can't be converted (via is_numeric) before running this query. So the assumption was apparently that when that "checking" returns that everything inside that sku can be converted, it can be reliably done. But the fact that the conversion might be run before the sku filter runs ... I guess no one thought of that... Thanks for your blog posts, too - good stuff! – Florian Peschka Apr 10 '17 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.