Unicode lost during deployment of ntext columns

Glebby

I have a table on Azure database, with the following structure

CREATE TABLE [dbo].[cmsPropertyData](
	[id] [INT] IDENTITY(1,1) NOT NULL,
	[contentNodeId] [INT] NOT NULL,
	[versionId] [UNIQUEIDENTIFIER] NULL,
	[propertytypeid] [INT] NOT NULL,
	[dataInt] [INT] NULL,
	[dataDate] [DATETIME] NULL,
	[dataNvarchar] [NVARCHAR](500) NULL,
	[dataNtext] [NTEXT] NULL,
	[dataDecimal] [DECIMAL](20, 9) NULL,
 CONSTRAINT [PK_cmsPropertyData] PRIMARY KEY CLUSTERED (	[id] ASC)

SQL Data Compare (I use 12.1) generates the following deployment script

UPDATE [dbo].[cmsPropertyData] SET [dataNtext]=cast(N'[
    "caption": "آفتح حساب",
]' COLLATE SQL_Latin1_General_CP1_CI_AS as varchar(max))
 WHERE [id] = 50897

Please note it casts ntext to varchar, whereas it should cast to nvarchar

As a result, the resulting value in the target table becomes

[     "caption": "???? ????", ]

sam.blackburn

We're doing some work in a related area - I think we should have a fix for this issue in our next release!

mattchandler

Our latest frequent updates release contains a fix which should have fixed this issue.

Matthew Chandler
Software Developer on SQL Compare and SQL Data Compare