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.

Answer 1

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