U4-4159 - SqlException thrown during scheduled publishing

Created by Johannes J. 29 Jan 2014, 18:24:20 Updated by Shannon Deminick 26 Jun 2017, 06:40:03

Relates to: U4-5513

Relates to: U4-5641

This appears about every minute in my Umbraco trace log. From what I see in past logs, this starts immediately after installation (Umbraco 7.0.1) and is still present after an upgrade to 7.0.2.

The SQL error message says: "The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value."

2014-01-29 19:06:28,432 [6] INFO Umbraco.Core.Persistence.UmbracoDatabase - [Thread 8] bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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 Umbraco.Core.Persistence.Database.<Query>d__1c1.MoveNext() 2014-01-29 19:06:28,432 [6] ERROR umbraco.presentation.publishingService - [Thread 8] Error executing scheduled publishing System.Data.SqlClient.SqlException (0x80131904): Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs. bei System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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 Umbraco.Core.Persistence.Database.<Query>d__1c1.MoveNext() bei System.Collections.Generic.List1..ctor(IEnumerable1 collection) bei System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) bei Umbraco.Core.Persistence.Repositories.ContentRepository.<PerformGetByQuery>d__1f.MoveNext() bei System.Linq.Enumerable.WhereSelectEnumerableIterator2.MoveNext() bei System.Linq.Buffer1..ctor(IEnumerable1 source) bei System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source) bei umbraco.presentation.publishingService.CheckPublishing(Object sender) ClientConnectionId:16ee3d23-3bd0-4982-a8c4-b8187e98a885

1 Attachments

Comments

Johannes J. 01 Feb 2014, 21:13:48

This stopped happening today, February 1. The last log entry with this exception was from Jan 31, 23:59.

The first installer run was on January 2nd, the install was completed January 19th.

Not sure if I should set this to Resolved, maybe someone else has been able to reproduce this?


Johannes J. 24 Feb 2014, 21:50:14

This is happening again. The messages started (occurring every minute) on February 13th, 00:00h. On February 14th, I upgraded to Umbraco 7.0.3, the exceptions have been continuing since then.


Dan Booth 21 Mar 2014, 09:09:37

This sounds similar to an issue I've seen in Umbraco 6.1.6 mentioned in this thread http://our.umbraco.org/forum/ourumb-dev-forum/bugs/47059-Trace-Log-filling-with-UmbracoCorePersistenceUmbracoDatabase-warning


Biagio Paruolo 18 Sep 2014, 15:32:59

I've the same error on 6.2.2. See ISSUE : http://issues.umbraco.org/issue/U4-5513


Dan Booth 18 Sep 2014, 19:20:33

Have you tried changing the database user language to 'English'? That's what fixed it for me.


Biagio Paruolo 18 Sep 2014, 22:17:02

Are you sure that this language change do not cause any issue to the date post or to other datas?


Biagio Paruolo 19 Sep 2014, 06:36:22

I had an old Umbraco 4.x installation and I check SQL config. It's into italian language and schedule works without any issue.


Biagio Paruolo 19 Sep 2014, 06:48:21

I tried but the problem is still there.


Biagio Paruolo 25 Sep 2014, 21:04:44

Have you other suggestions? Where are saved scheduled data into Umbraco db?


Dan Booth 25 Sep 2014, 21:15:57

Scheduled data isn't stored in the DB. A service calls a controller every minute that then executes a database query to return all nodes that are due to be published (using ContentService.GetContentForRelease()). See https://github.com/umbraco/Umbraco-CMS/blob/eae00873073f20c60e355ec6e95ff6259ad2652b/src/Umbraco.Core/Publishing/ScheduledPublisher.cs

My problem arose when I created the database using English but then restored it to a different server where the user's language was set as British English. When I changed the user's language back to English the problem went away. If that doesn't work for you, I'm sorry. Maybe ensure that the language is the consistent ie. if created in Italian that it still is using Itlaian?


Biagio Paruolo 25 Sep 2014, 21:40:18

I need to find the code of GetContentForRelease. Maybe that the error is raised into that function. I found this query: SELECT D.nodeId, D.releaseDate, D.expireDate FROM dbo.cmsDocument D WHERE D.newest = 1 AND ( ( D.releaseDate IS NOT NULL AND D.releaseDate <= GETDATE() ) OR ( D.expireDate IS NOT NULL AND D.expireDate <= GETDATE() ) ); and if I use this methods: var doc = new Document(nodeId); var publishAt = doc.ReleaseDate; var removeAt = doc.ExpireDate;

I can publish/unpublish content nodes, cicling on them.


Biagio Paruolo 26 Sep 2014, 07:29:53

I tried that query and I've: 2772 NULL 2014-05-23 00:00:00.000 3555 2014-09-19 16:12:00.000 NULL 3573 2014-09-19 15:57:00.000 NULL 3580 2014-09-19 09:05:00.000 NULL 2809 2014-05-22 00:00:00.000 NULL So, to solve "publish at" problem, I can cycle on this rows and for NodeID ( see first column ), I publish/unpublish node.


Biagio Paruolo 26 Sep 2014, 12:42:18

When I schedule a date to publish a node, I've these errors into the Umbraco log file. Why?

2014-09-26 14:40:12,895 [41] ERROR Umbraco.Web.WebServices.ScheduledPublishController - [Thread 43] Error executing scheduled task System.Data.SqlClient.SqlException (0x80131904): La conversione di un tipo di dati varchar in datetime ha generato un valore non compreso nell'intervallo dei valori consentiti. in System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) in System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) in System.Data.SqlClient.SqlDataReader.Read() in Umbraco.Core.Persistence.Database.<Query>d__1c1.MoveNext() in System.Collections.Generic.List1..ctor(IEnumerable1 collection) in System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) in Umbraco.Core.Persistence.Repositories.ContentRepository.<PerformGetByQuery>d__22.MoveNext() in System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext() in Umbraco.Core.Publishing.ScheduledPublisher.CheckPendingAndProcess() in Umbraco.Web.WebServices.ScheduledPublishController.Index() ClientConnectionId:009297fe-e144-4705-8459-4982fa99e930 2014-09-26 14:40:12,911 [41] ERROR Umbraco.Web.Scheduling.ScheduledPublishing - [Thread 10] An error occurred with the scheduled publishing System.Net.WebException: Errore del server remoto: (400) Richiesta non valida. in System.Net.WebClient.UploadDataInternal(Uri address, String method, Byte[] data, WebRequest& request) in System.Net.WebClient.UploadString(Uri address, String method, String data) in Umbraco.Web.Scheduling.ScheduledPublishing.Start(ApplicationContext appContext) 2014-09-26 14:40:32,083 [41] ERROR Umbraco.Core.Services.NotificationService - [Thread 32] An error occurred sending notification System.Net.Mail.SmtpFailedRecipientException: Nome di cassetta postale non consentito. Risposta del server: sorry, that domain isn't allowed to be relayed thru this MTA (#5.7.1) (mail405.ha.ovh.net, smtpauth: no) in System.Net.Mail.SmtpTransport.SendMail(MailAddress sender, MailAddressCollection recipients, String deliveryNotify, Boolean allowUnicode, SmtpFailedRecipientException& exception) in System.Net.Mail.SmtpClient.Send(MailMessage message) in Umbraco.Core.Services.NotificationService.<>c__DisplayClass8.b__7(Object state) 2014-09-26 14:41:12,911 [41] INFO Umbraco.Core.Persistence.UmbracoDatabase - [Thread 10] in System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) in System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) in System.Data.SqlClient.SqlDataReader.Read() in Umbraco.Core.Persistence.Database.<Query>d__1c1.MoveNext() 2014-09-26 14:41:12,911 [41] ERROR Umbraco.Web.WebServices.ScheduledPublishController - [Thread 10] Error executing scheduled task System.Data.SqlClient.SqlException (0x80131904): La conversione di un tipo di dati varchar in datetime ha generato un valore non compreso nell'intervallo dei valori consentiti. in System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction) in System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) in System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows) in System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more) in System.Data.SqlClient.SqlDataReader.Read() in Umbraco.Core.Persistence.Database.<Query>d__1c1.MoveNext() in System.Collections.Generic.List1..ctor(IEnumerable1 collection) in System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) in Umbraco.Core.Persistence.Repositories.ContentRepository.<PerformGetByQuery>d__22.MoveNext() in System.Linq.Enumerable.WhereEnumerableIterator1.MoveNext() in Umbraco.Core.Publishing.ScheduledPublisher.CheckPendingAndProcess() in Umbraco.Web.WebServices.ScheduledPublishController.Index() ClientConnectionId:009297fe-e144-4705-8459-4982fa99e930 2014-09-26 14:41:12,911 [41] ERROR Umbraco.Web.Scheduling.ScheduledPublishing - [Thread 32] An error occurred with the scheduled publishing System.Net.WebException: Errore del server remoto: (400) Richiesta non valida. in System.Net.WebClient.UploadDataInternal(Uri address, String method, Byte[] data, WebRequest& request) in System.Net.WebClient.UploadString(Uri address, String method, String data) in Umbraco.Web.Scheduling.ScheduledPublishing.Start(ApplicationContext appContext)


Biagio Paruolo 03 Oct 2014, 14:35:09

Any update and help?


Martin Griffiths 25 Nov 2014, 10:09:07

Has anyone on the core team looked into this issue?


Shannon Deminick 26 Nov 2014, 03:56:11

@Mmartinjgriffiths Yes, see last comment here: http://issues.umbraco.org/issue/U4-5641#comment=67-16703 that you linked to this issue.

plase try upgrading to the lastest version and report back


Martin Griffiths 26 Nov 2014, 10:14:55

@Shandem So are we saying all of the items are related to proper SQL parameters? If you dig through there's quite a lot of people reporting similar issues. I've tried to link a few, but i'm 100% its the same problem.


Biagio Paruolo 26 Nov 2014, 10:22:31

Hi, I tried to change SQL parameter and db proprierties, but that do not solve the problem. I upgrade to 6.2.1 without success. So, Do you say that we need to upgrade to 6.2.4?


Shannon Deminick 27 Nov 2014, 22:29:29

I'm not sure you you 'tried to change SQL parameter and db properties', what I am talking about is in 6.2.4, we change the way that we were querying for data. Before 6.2.4, we were sending the parameters to SQL server formatted manually/inline in the SQL statement. In 6.2.4 we are sending the SQL parameters in each SQL query as real SQL parameters and letting .Net take care of the data translations. A DateTime in an SQL parameter will be dealt with in .Net instead of us trying to format it correctly.


Shannon Deminick 26 Jun 2017, 06:40:03

Closing issue due to inactivity - see blog post for details https://umbraco.com/blog/issue-tracker-cleanup/


Priority: Critical

Type: Bug

State: Closed

Assignee:

Difficulty: Difficult

Category: Architecture

Backwards Compatible: True

Fix Submitted:

Affected versions: 6.1.3, 7.0.1, 7.0.2, 6.2.2

Due in version:

Sprint:

Story Points:

Cycle: