SqlServer Transport for NServiceBus 7 shows chinese characters in message body column

Question

We updated to NSB 7 and now use Sql Server for the transport and persistence.

(Side note: this works after updating a lot of messages, because somehow they became incompatible as the serializer was changed from Newtonsoft.Json to NServicebus.NewtonsoftSerializer. All validation logic has to be removed from the message class constructors.)

OK, so in SSMS 17.8.1 (latest), when I open the table for an endpoint the BodyString column shows Chinese characters. The text is not garbage it's just Chinese.

So my question: why does the computed column show Chinese characters? The collation in the database is the default.

 var transport = endpointConfiguration.UseTransport<SqlServerTransport>();
 transport.ConnectionString( connectionString );
 transport.DefaultSchema( "dbo" );
 transport.UseSchemaForQueue( "error", "dbo" );
 transport.UseSchemaForQueue( "audit", "dbo" );
 //this shows Chinese text
 transport.CreateMessageBodyComputedColumn();

Why is that and what am I missing here? This is .NET 4.72, the transport is an Azure Sql database.

Please test the serializer https://stackoverflow.com/questions/32406513/serializing-foreign-languages-using-json-net — Alberto Morillo, Aug 13 '18 at 22:08
@AlbertoMorillo I am not the one who (de)serializes. NServiceBus does. The BodyString column is a computed column. I have to assume they tested this before reelease? Not sure what I am missing here. Thanks. — John, Aug 15 '18 at 12:32
Have you tried to test with previous version of NSB? Just to make is not a case of software regression. — Alberto Morillo, Aug 15 '18 at 15:20
@AlbertoMorillo I have but the serializer is different. Version 7 has a new NServicebus.NewtonsoftSerializer package. I am hoping someonee from Particular can help here on SO. — John, Aug 16 '18 at 11:23

score 2 · Answer 1 · answered Nov 26 '18 at 08:59

The BodyString should cast the value to varchar and not nvarchar

See: https://docs.particular.net/transports/sql/operations-scripting#inspecting-messages-in-the-queue"

This was a bug in the calculated column definition which has been corrected. The BodyString column is not used by NServiceBus itself and only present for diagnostical reasons for operators. So a non critical bug in the schema that is created by NServiceBus.

https://github.com/Particular/NServiceBus.SqlServer/pull/453

The is going to be fixed in the next maintenance release for the NServiceBus SQL Transport.

Please manually modify the schema to correct this manually by updating the schema.

Please do the following:

ALTER TABLE [MyQueueTable] DROP COLUMN BodyString;
GO

Followed by:

ALTER TABLE [MyQueueTable] ADD BodyString as cast(Body as varchar(max));
GO

SqlServer Transport for NServiceBus 7 shows chinese characters in message body column

1 Answers1