Challenge:
While upgrading our Sitecore Solution from 6.3.1 110113 to 64.1 120113, which was to be done as a 2 step process, (i.e. from 6.3.1 110113 to the prerequisite version 6.4.0 Initial Release, and then move to 6.4.1 120113) we found the following error in our Sitecore Log files.
1956 06:43:02 ERROR Exception in alarm clock event subscriber.
Exception: System.Exception
Message: Invalid column name ‘Sequence’.
Source: Sitecore.Kernel
at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
at Sitecore.Data.DataProviders.Sql.DataProviderReader..ctor(DataProviderCommand command)
at Sitecore.Data.DataProviders.Sql.SqlDataApi.CreateReader(String sql, Object[] parameters)
at Sitecore.Data.DataProviders.Sql.SqlDataApi.<CreateObjectReader>d__0`1.MoveNext()
at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
at Sitecore.Data.Eventing.SqlEventQueue.GetTimestampForLastProcessing()
at Sitecore.Eventing.EventQueue.GetQueuedEvents(String targetInstanceName)
at Sitecore.Eventing.EventQueue.ProcessEvents(Action`2 handler)
at Sitecore.Eventing.EventProvider.RaiseQueuedEvents()
at Sitecore.Services.AlarmClock.Heartbeat_Beat(Object sender, EventArgs e)
Nested Exception
Exception: System.Data.DataException
Message: Error executing SQL command: SELECT MAX([Created]), MAX([Sequence]) FROM [EventQueue]
Nested Exception
Exception: System.Data.SqlClient.SqlException
Message: Invalid column name ‘Sequence’.
Source: .Net SqlClient Data Provider
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
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.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at Sitecore.Data.DataProviders.Sql.DataProviderCommand.ExecuteReader()
Which actually said it was searching for a Column called “Sequence” in EventQueue table, which it was unable to get.
Findings and Research:
On investigation, we found that in while upgrading to the version 6.3.1 110113 Sitecore had back-ported a functionality from 6.4.1 where it had deprecated and removed the Sequence column from EventQueue Table.
For more details, please refer to the following Release Notes link: http://sdn.sitecore.net/Products/Sitecore%20V5/Sitecore%20CMS%206/ReleaseNotes/ChangeLog/Release%20History%20SC63.aspx
Thus the column was already removed from our Databases.
But, after following the Log Entries and checking the code in Reflector, we moved to a function in Sitecore.Data.Eventing.SqlEventQueue in 6.4.0 Initial Release where it was hardcoded to read values from this column.
[sourcecode highlight=”10″ language=”csharp”]
protected override EventQueue.TimeStamp GetTimestampForLastProcessing()
{
if (this.timestamp == null)
{
string sql = " SELECT MAX({0}Created{1}), MAX({0}Sequence{1}) FROM {0
}
EventQueue{1}";
this.timestamp = this.DataApi.CreateObjectReader(sql, new object[0],delegate (IDataReader reader) { return new EventQueue.TimeStamp(GetDateTime(reader, 0), GetLong(reader, 1)); }).FirstOrDefault();
if (this.timestamp == null)
{
this.timestamp = EventQueue.TimeStamp.MinValue;
}
}
returnthis.timestamp;
}
[/sourcecode]
protected override EventQueue.TimeStamp GetTimestampForLastProcessing()
{
if (this.timestamp == null)
{
string sql = " SELECT MAX({0}Created{1}), MAX({0}Sequence{1}) FROM {0
}
EventQueue{1}";
this.timestamp = this.DataApi.CreateObjectReader(sql, new object[0],delegate (IDataReader reader) { return new EventQueue.TimeStamp(GetDateTime(reader, 0), GetLong(reader, 1)); }).FirstOrDefault();
if (this.timestamp == null)
{
this.timestamp = EventQueue.TimeStamp.MinValue;
}
}
returnthis.timestamp;
}
[/sourcecode]
Solution:
We contacted Sitecore Support with all this information, and the reply was as follows:
“>>So if it is backported in 6.3.1 110112 then this error should not come when we upgrade our solution to 6.4.0 Initial release right? (Correct me if I am wrong!)
Pay attention:
This is an old update. We recommend that you install the most recent update. You should only use this update as an interim update when upgrading from Sitecore 6.3 to 6.4 or later. (http://sdn.sitecore.net/Products/Sitecore%20V5/Sitecore%20CMS%206/Update/6_4_0_rev_101012.aspx) (There is a little typo in this sentence and I’ll make a request to our Doc team to fix that. You should upgrade to the Sitecore CMS 6.4.1 rev.101221 version or later )
If you continue upgrading your solution to the Sitecore CMS 6.4.1 rev.101221 version or later this will solve the issue. These versions use the Stamp column of the EventQueue table instead of the Sequence column.
(Sitecore Link for 6.4.1 Initial Release and further)
>>What can be the fix for this error?
I suppose this could be fixed by executing an SQL script which will return the Sequence column and delete the Stamp one. But I strongly recommend you to continue the upgrading process since this version is just an intermediate one.”
Great Guys really! they solve your issues and problems so quickly! Special Thanks to Viacheslav Nemirovskiy for looking into the issue and solving it!
Happy Sitecore Upgrading! 🙂
I’m working with Sitecore at a Sitecore partner for three months now, and I really like it. Ok, the Desktop interface is slow as hell but the whole system is generally of pretty high quality. For my graduation paper I’ve build a high performance plug-in system for it and I replaced some of the parts of Sitecore to output something other then HTM and it works flawless.