This document explains how to patch a system using the SQL Server transport to allow message headers to contain characters not supported by the current SQL Server collation. The issue may cause data loss when such characters are used in header names or values.
Any of the supported affected minor versions (3.1.x, 3.0.x, or 2.2.x) should be updated to the latest patch release. Older (unsupported) affected versions should be updated to a newest supported minor (2.1.x or 2.0.x) or major version (1.x).
To upgrade an existing endpoint:
- Update to the latest patch release
- Deploy the new version
- Check endpoint startup logs for a warning message
- Follow the queue table schema upgrade procedure
The SQL Server transport detects incorrect definition of the
Headers column and logs a warning:
Table [dbo].[SampleEndpoint] stores headers in a non Unicode-compatible column (varchar).
This may lead to data loss when sending non-ASCII characters in headers. SQL Server transport 3.1 and newer can take advantage of the nvarchar column type for headers. Please change the column type in the database.
If this log event is written to the log file then read the following guidance on how to upgrade the queue table schema.
Headers column definition on existing queue tables needs to be updated manually using the following SQL statement for every queue table managed by a given endpoint:
declare @queueTableName nvarchar(max) = N'...' declare @sql nvarchar(max) = N'alter table ' + @queueTableName + N' alter column Headers nvarchar(max) not null'; exec sp_executesql @sql;