When we first implemented SQL Anywhere support in our product, we used to get System.FormatException errors when we read a DATETIMEOFFSET from our database tables. As part of the work around to that problem, we added the following lines to our database schema update script at the very end:
This has been working fine for us. Until last week, when we upgrade to EBF 3967. Now, we get the following stack trace when we try to read a DATETIMEOFFSET from one of our tables:
> 2014-09-15 09:14:08,765 [PubSubBufferMemory::RunSecondaryOutput] ERROR PubSubBufferMemory::PubSubBufferMemory - PubSubBufferMemory::RunSecondaryOutput failed while transitioning from Running to Running: System.Exception: Secondary buffer has failed. Persistent node connection rejected. > ---> LPRCore.CarSystem.DataAccessException: A database error occurred while trying to retrieve the versions to transmit in GetMessage. > ---> System.Data.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. > ---> System.FormatException: String was not recognized as a valid DateTime. > at iAnywhere.Data.SQLAnywhere.SACommand._ExecuteReader(CommandBehavior commandBehavior, Boolean isExecuteScalar, Boolean isBeginExecuteReader) > at iAnywhere.Data.SQLAnywhere.SACommand.ExecuteDbDataReader(CommandBehavior behavior) > at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior) > at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) > --- End of inner exception stack trace --- > at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior) > at System.Data.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues) > at System.Data.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption) > at System.Data.Objects.ObjectQuery`1.System.Collections.Generic.IEnumerable<t>.GetEnumerator() > at LPRCore.CarSystem.UserDataAccessor.<queryuserversions>d__1a.MoveNext() in c:\ElsagTFS\EOC4\CarSystem\LPRCore Plugin CarSystem\UserDataAccessor.cs:line 605 > at LPRCore.CarSystem.MultilistEnumerator`2.<getenumerator>d__5.MoveNext() in c:\ElsagTFS\EOC4\CarSystem\LPRCore Plugin CarSystem\MultilistEnumerator.cs:line 24 > at System.Linq.Buffer`1..ctor(IEnumerable`1 source) > at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) > at LPRCore.Devices.PubSubBufferSA.<getmessages>d__7c.MoveNext() in c:\ElsagTFS\EOC4\CarSystem\LPRCore Plugin CarSystem\PubSubBufferSA.cs:line 1106 > --- End of inner exception stack trace --- > at LPRCore.Devices.PubSubBufferSA.<getmessages>d__7c.MoveNext() in c:\ElsagTFS\EOC4\CarSystem\LPRCore Plugin CarSystem\PubSubBufferSA.cs:line 1109 > at LPRCore.Devices.PubSubBufferMemory.RunSecondaryOutput(Object subscription) in c:\ElsagTFS\EOC4\LPRCore\LPRCore Plugin Com\PubSubBufferMemory.cs:line 214 > --- End of inner exception stack trace ---
The code in question is an Entity Framework query that retrieves one row from one table in the database. Here's the CREATE TABLE command for the table in question:
The EF query:
We currently do not have the ability to download more recent updates; 3967 is the last EBF that we downloaded before the switch to SAP's download site. I'm unclear as to why we don't have the necessary privileges to download from SAP's site.
In any event, I've re-run the section of the script that I pasted into this question & the error still occurs. Can someone help me figure out how I can get this to work with this EBF? If we can't get it to work, we'll have to rollback the upgrade to 3967.
It turns out that another developer here upgraded the project to use EBF 4155 without my knowing it. He had asked me for the latest build I had, and I gave him 3967, which was the latest I had. I didn't know that I was running the wrong DLLs on my local workstation until we discussed this problem today.
Once I removed the SQL Anywhere DLLs for EBF 3967 from the GAC, everything worked properly.
I am getting the EBF installer from him & I will install it on my workstation, and this problem won't happen again.
FWIW, here are two fixes w.r.t. DATETIMEOFFSET in newer v12.0.1 EBFs (well, fixed apparently in builts .3968 and .3971 (so a "near miss", as they say):
Can you make sure that the following option value does apply to connections taken from the pool (in case you are using pooled connections)?