Query fails from application but runs fine in SSMS

0 votes
1 answer
1051 views
sql-server-2012 ssms c# type-conversion nhibernate
                          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("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
                        
Asked by F.P (207 rep)
Apr 10, 2017, 12:11 PM
Last activity: Apr 10, 2017, 01:16 PM
Query fails from application but runs fine in SSMS

Related Questions
