Upgrade to Sql Server 2014 - CleanupProcessedMessages errors

[Pete Johnson]

We have just migrated our messagebus implementation to a new SQL Server 2014 instance (we have the same language and collation on old and new) and we seem to be getting the following error repeatedly in our logs:

12:24:06.0039|T12|M|Error|SQLMT_sql://MessageBus/Q_SlatePrintManagerHub|Error deleting processed messages: System.Data.SqlClient.SqlException (0x80131904): The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

The statement has been terminated.

   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)

   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)

   at System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async, Int32 timeout, Boolean asyncWrite)

   at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite)

   at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()

   at NGinnBPM.MessageBus.Impl.SqlMessageTransport2.CleanupProcessedMessages()

We are in the UK ('dd/mm/yy') and it is 28/10/2014 today.

Looking at the code, the following SQL statement is used (NamedQueries.sqlserver.json):

"delete top(10000) {0} with(READPAST) where retry_time <= @lmt and subqueue='X'"

(where @lmt is set to DateTime.Now - MessageRetentionPeriod, i.e. 12 hours ago)

I can replicate this error by running the following against the DB:

declare @lmt varchar(50)

set @lmt = '28/10/2014 01:05:48'

select top(1000) * from Q_Mybus with(READPAST) where retry_time <= @lmt and subqueue='X'

This is because the value of DateTime.Now is dd/MM/yy and SQL server expects MM/dd/yy.

The only way I can think to fix this is to change CommonQueueOps.CleanupProcessedMessages line 300 to:

 _sql.AddParameter(cmd, "lmt", lmt.ToString("dd MMM yy HH:mm:ss"));

Is there anything else we can do?

Thanks

[Rafal Gwizdala]

Pete, this is quite strange as we're using a DateTime parameter in that query, not a string, so date formatting should not be an issue. But i'll check this - certainly something that can break in other places too if DateTime parameters behavior depend on sql server regional settings. 

--
You received this message because you are subscribed to the Google Groups "nginn-messagebus" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nginn-messageb...@googlegroups.com.
To post to this group, send email to nginn-me...@googlegroups.com.
Visit this group at http://groups.google.com/group/nginn-messagebus.

[Pete Johnson]

I tested it in linqpad using a DbCommand the AddParameter code from SqlAbstractions and it worked without error - so I'm not sure what's happening.

void Main()

{

var lmt = new DateTime(2014,10,27);

using (var conn = new SqlConnection("data source=.;initial catalog=MessageBus;persist security info=True;Trusted_Connection=true;MultipleActiveResultSets=true;"))

{

conn.Open();

using (DbCommand cmd = conn.CreateCommand())

{

cmd.CommandText = "select top(1000) * from Q_MyBus with(READPAST) where retry_time <= @lmt and subqueue='X'";

AddParameter(cmd, "lmt", lmt);

var n = cmd.ExecuteReader();

n.Dump();

}

conn.Close();

}

}

// From SqlAbstractions.cs

public static void AddParameter(DbCommand cmd, string parameterAlias, DateTime? value)

{

    IDataParameter para = cmd.CreateParameter();

            para.DbType = DbType.DateTime;

            para.Value = value.HasValue ? new SqlDateTime(value.Value) : SqlDateTime.Null;

            para.Direction = ParameterDirection.Input;

            para.ParameterName = parameterAlias;

            cmd.Parameters.Add(para);

}

[Rafal Gwizdala]

Pete, can you check what exactly SQL is sent to the database in each case? SQL profiler should show that...

--

[Rafal Gwizdala]

one more thing - i'm not able to reproduce the problem, probably because of different regional settings and older SQL version

but i've made a change in the source, replacing DbType.DateTime parameter type with DbType.DateTime2, and published a new version with these changes on Nuget (1.1.7)

can you test this?

[PJohnson]

Yes I will test this and let you know the result.

Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "nginn-messagebus" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/nginn-messagebus/ZMnOAxoMXwM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to nginn-messageb...@googlegroups.com.

[Rafal Gwizdala]

PS maybe you're using two different accounts for testing - one sql server account for the message bus and windows account for your test code?

if so, maybe they have different language set?

pls have a look at http://msdn.microsoft.com/en-us/library/ms187378.aspx - maybe changing a default language for sql login would help

R

[Pete Johnson]

The sql login on the original server was US english and on the new server it is British. The user account that nginn runs under on both servers is UK English - so I would have thought it would be a problem on the old server not the new one where the date formats match.

Running profiler the statement that's being run is:

delete top(10000) MessageBus.dbo.Q_MyBus with(READPAST) where retry_time <= '2014-10-19 12:26:18' and subqueue='X'

Which uses a japanese-style date which should be fine on any language.

I've not had chance upgrade via nuget yet - I'll have to push to production so it might take a while.

Thanks

[Pete Johnson]

Pushed the new version live now - everything working fine. Thanks! 

[Rafal Gwizdala]

wow, that's a mystery

but i''m glad everything's fine

R

On Tue, Nov 11, 2014 at 4:29 PM, Pete Johnson <petecj...@gmail.com> wrote:

Pushed the new version live now - everything working fine. Thanks! 

--