I have just upgraded a website to the latest version of Umbraco 8 (v8.8.0) and the latest version of Vendr (1.3.1) and everything works fine locally.
I have uploaded the website and copy of the database to remote hosting however the analytics screen is showing me the following:
And the logs have the following types of entries:
{"@t":"2020-10-06T20:44:39.8404720Z","@mt":"Unhandled controller exception occurred for request '{RequestUrl}'","@l":"Error","@x":"System.Data.SqlClient.SqlException (0x80131904): The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.\r\n at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\r\n at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)\r\n at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)\r\n at System.Data.SqlClient.SqlDataReader.Read()\r\n at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)\r\n at System.Data.SqlClient.SqlCommand.ExecuteScalar()\r\n at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 334\r\n at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.
I have managed to recreate the same issue locally, I notice that the default language for the database user locally is set to "English - us_english" however if I change this to British English - English I get the same errors as above.
Unfortunately I am unable to control the language of the database user in the hosting environment and it defaults to British English - English so currently have no way around making this work.
Thanks Graham and thanks for doing some digging to work out the root cause. I know the area that is the cause of this so will just have to try and find a workaround.
I've reported this on our issue tracker at https://github.com/vendrhub/vendr/issues/198 I will keep the conversation going there but will report back with a resolution once one is found.
It ultimately comes from some code where we try and convert a datetime in SQL to just the date so that we can do easy comparisons. We were previously using CONVERT(datetime, CONVERT(nvarchar(10), {input}, 120)) but this seems to result in the error above if the DB local isn't right. I've now updated it to use the method DATEADD(dd, DATEDIFF(dd, 0, {input}), 0) which should still give you just the date part of a date, but avoids the conversion from nvarchar so should avoid the issue.
Vendr Analytics Screen Errors
Hey Matt,
I have just upgraded a website to the latest version of Umbraco 8 (v8.8.0) and the latest version of Vendr (1.3.1) and everything works fine locally.
I have uploaded the website and copy of the database to remote hosting however the analytics screen is showing me the following:
And the logs have the following types of entries:
{"@t":"2020-10-06T20:44:39.8404720Z","@mt":"Unhandled controller exception occurred for request '{RequestUrl}'","@l":"Error","@x":"System.Data.SqlClient.SqlException (0x80131904): The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.\r\n at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)\r\n at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)\r\n at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)\r\n at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)\r\n at System.Data.SqlClient.SqlDataReader.Read()\r\n at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)\r\n at System.Data.SqlClient.SqlCommand.ExecuteScalar()\r\n at StackExchange.Profiling.Data.ProfiledDbCommand.ExecuteScalar() in C:\projects\dotnet\src\MiniProfiler.Shared\Data\ProfiledDbCommand.cs:line 334\r\n at Umbraco.Core.Persistence.FaultHandling.FaultHandlingDbCommand.Any ideas what the issue might be??
Graham
I have managed to recreate the same issue locally, I notice that the default language for the database user locally is set to "English - us_english" however if I change this to British English - English I get the same errors as above.
Unfortunately I am unable to control the language of the database user in the hosting environment and it defaults to British English - English so currently have no way around making this work.
If you can look into this that would be great.
Thanks Graham and thanks for doing some digging to work out the root cause. I know the area that is the cause of this so will just have to try and find a workaround.
I've reported this on our issue tracker at https://github.com/vendrhub/vendr/issues/198 I will keep the conversation going there but will report back with a resolution once one is found.
Matt
So I should have found and fixed the issue.
It ultimately comes from some code where we try and convert a datetime in SQL to just the date so that we can do easy comparisons. We were previously using
CONVERT(datetime, CONVERT(nvarchar(10), {input}, 120))but this seems to result in the error above if the DB local isn't right. I've now updated it to use the methodDATEADD(dd, DATEDIFF(dd, 0, {input}), 0)which should still give you just the date part of a date, but avoids the conversion from nvarchar so should avoid the issue.Fix will be in the 1.3.2 patch release
PS We have do something like the above as we need to support SQL CE so we can't just do
CONVERT(date, {input})as this isn't supported.That's perfect, many thanks for sorting so quickly, date times are a royal pain at the best of times!!
Thanks for getting this sorted into the upcoming patch release :)
Hi Graham.
1.3.2 has just been released containing this fix.
Thanks again for reporting
Matt
Amazing, thanks for sorting so quickly!!
is working on a reply...