Area names containing Unicode characters are corrupted (SQL Server only)

[peterhambly]

E.g. Slättåkra-Kvibille is corrupted

• Checked Postgres; all tables (shapefile, lookup) datatypes are OK. Windows displays them wrong (set to code page 1252); Notetab++ understands UTF-8 and the CSV file displays OK. SQL Server; converted data type from Text to NVARCHAR(1000); corruption is occuring in both databases, probably in tileMaker SELECTs.

[devilgate]

Isn't this front-end development? If the values are OK in both databases, and Windows is getting code page 1252, isn't that the problem? If we set the pages to be UTF-8, it should just work.

I'd be interested to see what I get on Mac. What would I need to do to see the relevant data?

[peterhambly]

Some examples from the US geography in Puerto Rico:

• Postgres appears to work fine. Previous fix to NVARCHAR(1000) has worked. SQL Server is still wrong. Problems is therefore in the SQL Server database. Effects database, hopefully not the various Java/Javascript drivers.

Postgres (correct):

sahsuland=> SELECT areaname from lookup_cb_2014_us_county_500k where cb_2014_us_county_500k = '01804540';
  areaname
------------
 Río Grande
(1 row)

SQL Server (wrong):

1> SELECT areaname from rif_data.lookup_cb_2014_us_county_500k where cb_2014_us_county_500k = '01804540';
2> go
areaname                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
R+-ío Grande                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        

(1 rows affected)

[devilgate]

Maybe an obvious question, but did you change the column type with the data in place? And if so, is the result any different if you insert the data after the column is NVARCHAR?

I recall seeing things like this with multiple languages, and if you changed the data type after the data was in, it didn't always do what you hoped.

[peterhambly]

The columns are all NVARCHAR(1000) all the way back to the initial load table. They indicates an issue with BULK INSERT the SQL Server load command Use Unicode Character Format to Import or Export Data:

1> SELECT [name]
2>   FROM [sahsuland].[peter].[cb_2014_us_county_500k]
3>  WHERE [countyns]  = '01804540';
4> go
name                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
R+¡o Grande                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         

(1 rows affected)

[peterhambly]

Fix by pull #152 Support Unicode in SQL Server BULK INSERT statements.