Unicode problems

Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
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.

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 Bronze 1
    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
    
This discussion has been closed.