Unicode problems

Brian Donahue

Hello,

As far as I'm aware, it hasn't been fixed in the software. What's happened is that there is binary data stored in a SQL Extended Property and SQL Compare Engine assumes that all EPs are text. When SQL Compare Engine resolves the binary data stored in an Extended Property as Unicode, it may encounter values that can't be transformed into Unicode text. I don't know of any workarounds at this point except to find and eliminate the errant extended property.

If you'd send me an email to support (AT) red-gate.com, I'll try to create an application for you that can find the extended property so you can remove it.

Brian Donahue

This also happens in the Data Compare portion of the packager. I've finally found the range of offending binary values that cause this problem. Anything between 0xdddddddd AND 0xffff will cause .net's SQLDataReader to throw an error because this range of values are ambiguous. They're basically telling the framework that the Unicode character continues past 16 bits, but then the next set of bytes aren't what's expected.

We're working on a fix for the problem. I've had a bit of difficulty trying to create a SQL query to find the offending data, but I've got one that will show any data that has one of the offending values as the entire column's value:

DECLARE @table nvarchar(80), @column nvarchar(80), @qry nvarchar(255)

DECLARE cColumns CURSOR FOR
select o.[name] AS [Table], c.name AS [Column] 
from syscolumns c INNER JOIN sysobjects o ON c.[id]=o.[id] 
where c.xtype IN 
(select xtype from systypes where name IN ('nchar', 'ntext', 'nvarchar')) AND o.xtype='U'

OPEN cColumns

FETCH NEXT FROM cColumns INTO @table, @column

WHILE @@FETCH_STATUS=0
BEGIN
	SELECT @qry='SELECT ['+@column+'] FROM ['+@table+'] WHERE CAST(['+@column+'] AS BINARY) BETWEEN 0xdddddddd AND 0xffff'
	exec sp_executesql @qry
	FETCH NEXT FROM cColumns INTO @table, @column
END

CLOSE cColumns
DEALLOCATE cColumns
GO