Inconsistent SQLDateTime Overflow with NHibernate

Question

We have a very strange error that sometimes we get this error when we want to save something from our WCF service. The object that we are saving contains NO invalid datetimes, we all check them before we save. When we see this error the database hangs sometimes and the WCF is in a faulty state. When I restart the DB and the IIS web app where the WCF is hosted and try to save again. It works.. We are clueless so if any one has some advice, please share

Following is the error:

2010-03-05 10:21:34,311 [5] ERROR ProjectX.Business.TTExceptionLogger - Exception somewhere in ReceiveResultsForMobile(): {0} Castle.Services.Transaction.CommitResourceException: Could not commit transaction, one (or more) of the resources failed ---> System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value) at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value) at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb) at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd) at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation) at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session) at NHibernate.Persister.Entity.AbstractEntityPersister.UpdateOrInsert(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session) at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Boolean hasDirtyCollection, Object[] oldFields, Object oldVersion, Object obj, Object rowId, ISessionImplementor session) at NHibernate.Action.EntityUpdateAction.Execute() at NHibernate.Engine.ActionQueue.Execute(IExecutable executable) at NHibernate.Engine.ActionQueue.ExecuteActions(IList list) at NHibernate.Engine.ActionQueue.ExecuteActions() at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session) at NHibernate.Event.Default.DefaultFlushEventListener.OnFlush(FlushEvent event) at NHibernate.Impl.SessionImpl.Flush() at NHibernate.Transaction.AdoTransaction.Commit() at Rhino.Commons.NHibernateTransactionAdapter.Commit() at Rhino.Commons.Facilities.RhinoTransactionResourceAdapter.Commit() at Castle.Services.Transaction.AbstractTransaction.Commit() --- End of inner exception stack trace --- at Castle.Services.Transaction.AbstractTransaction.Commit() at Castle.Services.Transaction.StandardTransaction.Commit() at Castle.Facilities.AutomaticTransactionManagement.TransactionInterceptor.Intercept(IInvocation invocation) at Castle.DynamicProxy.AbstractInvocation.Proceed() at IReceiveServiceProxy61c28a82c9a24e96957e32292b924889.Save(Receive instance) at WcfInterfaceService.MobileServices.SaveReceiveLines(IEnumerable1 receiveLines, String warehouseCode, String username, String deviceNumber, Boolean removeOldReceiveLines) in D:\Project Docs\Clients\ClientX 09.08\Projects\ProjectX\ProjectX.WcfInterfaceService\MobileServices.svc.cs:line 567 at WcfInterfaceService.MobileServices.ProcessReceiveResults(List1 receiveLines, String warehouseCode, String username, String deviceNumber) in D:\Project Docs\Clients\ClientX 09.08\Projects\ProjectX\ProjectX.WcfInterfaceService\MobileServices.svc.cs:line 770 at WcfInterfaceService.MobileServices.ProcessResultsFromMobile(String receiveResult, String warehouseCode, String username, String deviceNumber) in D:\Project Docs\Clients\ClientX 09.08\Projects\ProjectX\ProjectX.WcfInterfaceService\MobileServices.svc.cs:line 668

Answer 1

Are you running SQL Server 2008? I ran into this same error today when using SQL Server 2008. On the database I had set the column to "date" instead of "datetime" because I do not care about the time portion. But there isn't a "date" data type in .NET so you use datetime.

For me I was passing along null datetime values which defaults to something like 1/1/0001 12:00:00 AM. So I was getting the same error you are seeing because it included the time portion.

For me I had to make my datetime value nullable and I also had to use the MsSql2008Dialect in NHibernate which supports the date datatype. More info about NHibernate and SQL Server 2008 here.

I'd check to make sure your database data type is set correctly and that you are using the MsSql2008Dialect if you are using SQL Server 2008.

Answer 2

I have two questions before I can give you an answer:

1. What kind of database do you use?
2. What kind of date is causing the exception?

Guess: you are using a database that has a smaller datetime range or accuracy than you use in your code. In that case, the exception is not caused by NHibernate, but by a feature of the database. It's not a bug but a feature.