SQL Server Performance Forum – Threads Archive
little squares
OK, this is pretty dumb. I have a column from a table in my production database, and it’s being replicated to another database server.The column in both databases is nvarchar(40). In my production databse, I can read the words in the column, but in my reporting database, it becomes little squares. what the heck is it?? Anybody has any clues?
What do you use to report database? Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
If the NVARCHAR is used what is the default collation set for the database/table?
How about windows locale on the server and ensure similar version is used on client workstation too. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Luis,
I use transactional replication to replicate my production data to my reporting database. Satya,
How do I find out the default collation set and windows. Both versions are the same.
OK, I think I found out the collation, but that’s not the problem.
In my production database, this is the column property:
[creditCardName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL In my reporting database, the same column’s property is this:
[creditCardName] [nvarchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
One is a varchar, the other is a nvarchar. How do I fix this? Just by
alter table alter column varchar(40) This is a production database. One thing though, the little squares doesn’t appear everywhere. Most of them are readable, only some are not.
As far I know, there is no problem to change column form nvarchar to varchar, but wait for others post experts.
Luis Martin
Moderator
SQL-Server-Performance.com The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
This might seem silly, but are you using the same font to look at the different databases? If you have non-latin characters in your data, it may not be supported by your font–thus being displayed as little squares. If this isn’t it, can you give an example of the text as it appears in one db correctly, and as little squares in another?
True and this also depends on the Windows locale on the reporting server. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
I must be missing something here – how can you set a collation for an NVARCHAR column – is not the data in such a column Unicode data, and therefore no collation could apply? I would not change a column’s properties directly, but add a new column first (with the correct collation), copy the data into the new column — verify whether the results of the copy action are as expected — then drop the old column and then rename the new column. Besides that, I agree that if you see only blocks in text data then you are probably looking at a non-Unicode font. Check what font the actual client application is using.
In my production database, this is how the data looks like (data is varchar(40)) :
mastercard In reporting database (data is nvarchar(40)):
‘¬› °‘T“· (ok, this is weird, I see 4 squares and I copied and paste, but when I paste it, it gives me chinese characters???) I’m doing this from query analyzer and both fonts are courier new (western). Now they want me to change production from varchar(40) to nvarchar(40). I’m using transactional replication. Does anybody has any experience modifying the datatype of a column that is being replicated?
Here’s my understanding of setting collation on unicode fields: Yes, it’s a unicode field, and can hold any type of data. But, you must remember to use the ‘N’ designator when entering in data. Example:
update tblBlah set field = N'(foreign language text)’ If you don’t use the N designator, SQL server will try to interpret the data entered using the default collation. So, if you’re working on a database that only supports one collation, you can set it up to allow people to enter in ‘strings’ without the unicode designator. I’m sure there’s more to it than that, but that’s what I came across in a recent project.
tsusanto– I don’t see chinese characters. I see a slash, some ANSI drawing symbols, a degree symbol, and a T. That makes me think that your machine is collated, and when read by my (latin-collated) machine, it comes out different. The chinese characters that come out–is that what you expect? Does one of the servers have some type of language pack set up that the other doesn’t? Are you using QA from the same box, or from each of the servers themselves?
]]>